CODEWITHSUNDEEP
sqlsql-servert-sqlsubstring
GullitsMullet
GullitsMullet

Reputation: 358

SQL Server: Substring based on first two characters being a number

I have a text field and want to pull out an ID number from the field - the ID always starts with an 8 and is 12 characters long (e.g 899900014658), the current code uses the substring method below:

substring(textfield,charindex('8',textfield),12) as extractedID

This pulls out anything starting with an 8 though so I'm getting results like '8 am', '8 February' etc in the extractedID field.

Is there a method of extracting anything starting with an 8 where the second and third characters are also a number?

edit - solved using PATINDEX

 SUBSTRING(SubmissionDiaryEntry,(PATINDEX('%8[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',SubmissionDiaryEntry)),12)

Upvotes: 0

Views: 110

Answers (2)

Pawel Czapski
Pawel Czapski

Reputation: 1864

Ckeck below, it may help:

declare @textfield nvarchar(20) = '845456798234'

select case when substring(@textfield, 2, 1) like '[0-9]' 
                and substring(@textfield, 3, 1) like '[0-9]'  
            then substring(@textfield,charindex('8',@textfield),12) 
        else 'not valid'
        end 
        as extractedID

-- test
set @textfield = '8r5456798234'

select case when substring(@textfield, 2, 1) like '[0-9]' 
                and substring(@textfield, 3, 1) like '[0-9]'  
            then substring(@textfield,charindex('8',@textfield),12) 
        else 'not valid'
        end 
        as extractedID

however John's answer is much simplier and it looks like exactly what you are looking for. Below is just to clarify..., using John's script, answer to your issue would be:

select substring(textfield,charindex('8',textfield),12) as extractedID
from yourTable
where textfield like '8[0-9][0-9]%'

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

If I understand your question:

Select *
 From  YourTable
 Where textfield like '8[0-9][0-9]%'

Upvotes: 3

Related Questions