Forum Discussion

RayMilhon's avatar
RayMilhon
Copper Contributor
Jul 05, 2022

Converting a varchar field to Numeric

We have a field in our SQL Server 2012 DB that is a char(15) Field.  The Data in that field consists of codes that are actually 5 characters but the field was created with 15 for future expansion.  Most of the codes consist of 5 Numbers like 12345, however a few have 4 numbers and a letter 1234A  I need to run a query that converts the field to a numeric field but excludes the ones that have the alpha character.  I thought I could use cast or convert like the Val function in Access but instead of ignoring the alpha it errors.  What I want is to convert the field to a numeric and exclude any record where that field has a length of 4.  Any help is appreciated

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Jul 06, 2022

    Hi RayMilhon -- To find only records having numeric data, you can use the isnumeric function.  A simple example of its use is shown below.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver16 

    select * from aTable where isnumeric(colone) = 1

     

    But if I'm understanding correctly, it seems like you want to convert the existing column from char to numeric.  The only way that you can do that is to first delete all records with non-numeric data in the existing char column.  Otherwise, you ALTER TABLE statement will fail.

     

    Another alternative would be to create a new numeric column, hydrate it with numeric-only data from the existing char column (using the isnumeric function), then rename the old column and new columns so existing queries, stored procedures, views, etc. are not broken.  Take care.

Resources