CODEWITHSUNDEEP
sqlsql-server
Dev
Dev

Reputation: 367

Get numerical value between specified characters

I have column having value like 'aAb314282069480098c1234d#20e000'.
I would like to get only '314282069480098' from this. How can I achieve this.

I have tried like this:

SUBSTRING(Mercury_AcqRefData,PATINDEX('%[0-9]%',Mercury_AcqRefData),LEN(Mercury_AcqRefData))

but I didn't achieve. Thanks in advance for your help.

Upvotes: 4

Views: 63

Answers (3)

Bart Hofland
Bart Hofland

Reputation: 3905

Based on @Usman Mirza's answer, I created the following "one-liner" that uses your original field name:

LEFT(SUBSTRING([Mercury_AcqRefData],
               PATINDEX('%[0-9]%', [Mercury_AcqRefData]),
               LEN([Mercury_AcqRefData])),
     PATINDEX('%[^0-9]%', SUBSTRING([Mercury_AcqRefData],
                                    PATINDEX('%[0-9]%', [Mercury_AcqRefData]),
                                    LEN([Mercury_AcqRefData]))) - 1)

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

Can you please try the following query:

DECLARE @Mercury_AcqRefData AS VARCHAR(200) = 'aAb31428206948220098c1234d#20e000';

DECLARE @Filter1 AS VARCHAR (200) = '';
SELECT @Filter1 = SUBSTRING(@Mercury_AcqRefData, PATINDEX('%[0-9]%', @Mercury_AcqRefData), LEN(@Mercury_AcqRefData));
SELECT SUBSTRING(@Filter1, 0, PATINDEX('%[A-Za-z]%', @Filter1));

Upvotes: 3

UsmanMirza
UsmanMirza

Reputation: 276

Try below it is giving required results:

DECLARE @string varchar(200) = 'aAb314282069480098c1234d#20e000'
select left(s, patindex('%[^0-9]%', s) - 1)
FROM (SELECT SUBSTRING(@string, patindex('%[0-9]%', @string), len(@string)) 
as S
 ) XX;

Upvotes: 4

Related Questions