CODEWITHSUNDEEP
sqlsql-server-2005
Felipe Jordani
Felipe Jordani

Reputation: 33

SQL Server 2005 - how can I select a specific part of a string?

I have the string &string=45646874&v1=67&v2=test and from that I would like to grab the string 45646874 and sometime this extraction will have 8 characters sometimes 7 sometimes 5 etc.

So I need to search in &string=45646874&v1=67&v2=test and grab everything that comes after string= and before the next &

Upvotes: 3

Views: 35

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

SQL Server does not have any built in regex extraction support, but we can handle this using the base string functions as follows:

WITH cte AS (
    SELECT '&string=45646874&v1=67&v2=test' AS col UNION ALL
    SELECT 'blah blah &string=ABCDEF123&v1=hello&v2=goodbye'
)

SELECT
    SUBSTRING(col, CHARINDEX('&string=', col) + 8,
              CHARINDEX('&', col, CHARINDEX('&string=', col) + 1) -
              CHARINDEX('&string=', col) - 8) AS output
FROM cte;

enter image description here

Demo

Upvotes: 2

NiveaGM
NiveaGM

Reputation: 259

You can try this query

DECLARE @String varchar(100)= '&string=45646874&v1=67&v2=test'
SELECT SUBSTRING(@string,(CHARINDEX('string=',@string)+7),(CHARINDEX('&',@string,2)-(CHARINDEX('string=',@string)+7)))

Upvotes: 0

Related Questions