Convert String to Number in Query 2

[Bunting]

Hi there,

I wish to write a query which will retrieve a number of fields plus one calculated field. The field data.narrative may contain an account_id as its first 7 digits, I want to retrieve these digits and use it to link elsewhere in my database but the account_id must be a number to be usable.

Problem: once I have used the LEFT function to retrieve the first 7 digits, how can I convert this string to an integer which I can then compare to the account_id field elsewhere in my database? How will non-numeric characters in this string affect such an operation?

Many thanks.

 

[jbenson001]

So you are saying you have a string with a value something like '1234567ABC'?
If so, you can't convert this to a numeric data type.

 

[gmmastros]

Here's a sample...

Select Case When IsNumeric(Left(FieldName, 7) + 'e0') = 1
            Then Convert(int, Left(FieldName, 7))
            Else NULL
            End As Account_Id
From   TableName

Is Numeric will return 1 for data that can be converted to a number. The 'e0' will prevent data that is in scientific notation to 'slip through'.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Bunting]

Thanks George, that looks to be just what I'm looking for.

I do have a further question though. I should have mentioned, some of the Account_ID values have fewer than seven digits, is there a way to modify your query to check for 7 digit numbers, then 6, then 5, then discard any others which do not produce numbers? I had a go but could not figure it out.

 

[bborissov]

Based on George's example:

Select Case When IsNumeric(Left(FieldName, 7) + 'e0') = 1
            Then Convert(int, Left(FieldName, 7))

            When IsNumeric(Left(FieldName, 6) + 'e0') = 1
            Then Convert(int, Left(FieldName, 6))

            When IsNumeric(Left(FieldName, 5) + 'e0') = 1
            Then Convert(int, Left(FieldName, 5))

            Else NULL
            End As Account_Id
From   TableName

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Bunting]

Excellent, thanks Borislav - you and George have solved a problem I've been working on for some time.