CODEWITHSUNDEEP
sqlsql-servertype-conversion
Johnny Bones
Johnny Bones

Reputation: 8402

Determine if a Varchar field has a Numeric entry

I've got a field in a table that has a DataType of varchar(10). This field contains numeric values that are formatted as a varchar, for the sole purpose of being used to join two tables together. Some sample data would be:

AcctNum        AcctNumChar   
2223333          2223333   
3324444          3324444

For some records, the table sometimes thinks this field (AcctNumChar) is numeric and the join doesn't work properly. I then have to use an Update statement to re-enter the value as a varchar.

Is there any way to determine whether or not the field has a varchar or numeric value in it, using a query? I'm trying to narrow down which records are faulty without having to wait for one of the users to tell me that their query isn't returning any hits.

Upvotes: 1

Views: 5731

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use isnumeric() for a generic comparison, for instance:

select (case when isnumeric(acctnum) = 1 then cast(acctnum as decimal(10, 0))
        end)

In your case, though, you only seem to want integers:

(case when acctnum not like '%[^0-9]%' then cast(acctnum as decimal(10, 0)) 
 end)

However, I would strongly suggest that you update the table to change the data type to a number, which appears to be the correct type for the value. You can also add a computed column as:

alter table t add AcctNum_Number as 
    (case when acctnum not like '%[^0-9]%' then cast(acctnum as decimal(10, 0)) 
     end)

Then you can use the computed column rather than the character column.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use PATINDEX function:

DECLARE @s VARCHAR(20) = '123123'
SELECT PATINDEX('%[^0-9]%', @s)

If @s variable will have something different from range 0-9 this function will return the index of first occurence of non digit symbol. If all symbols are digits it will return 0.

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

IsNumeric worked, TRY_CONVERT didn't (SQL wouldn't recognize it as a built-in function for some reason). Anyway, for the record I ran the following query and got all of my suspect records:

SELECT *
FROM ACCT_LIST
where IsNumeric([ACCT_NUM_CHAR]) = 0

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use CAST and CONVERT (Transact-SQL) functions here to solve your purpose.

reference here - https://msdn.microsoft.com/en-IN/library/ms187928.aspx.

Upvotes: 0

gotqn
gotqn

Reputation: 43626

There are several ways to check if varchar column contains numeric value but I recommend to you to us TRY_CONVERT function.

It will give you NULL if the value cannot be converted to number. For example, to get all records that have numeric values, you can do this:

SELECT *
FROM [table]
WHERE TRY_CONVERT(INT, [value]) IS NOT NULL

Upvotes: 0

Related Questions