How to extract substring in SQL Server

Question

I have a field with something like okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos

I'd like to extract the name = value from the data.

How can I do that using SQL Server 2008 in efficient way? thank you

Raj More · Accepted Answer · 2011-01-28T17:12:41.947

5

Try this

;
With MyTable as
(
    SELECT 'okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos' FullString
    UNION 
    SELECT 'fkgbokfgbo&name=fokdofkd&okfaos' FullString
    UNION 
    SELECT 'okbogkbogkbokgbok' FullString
),
PatternIndex as
(
    Select 
        FullString + '&' FullString, 
        CharIndex ('&name=', FullString) + 1 LocationOfEqualSign, 
        CharIndex ('&', FullString, CharIndex ('&name=', FullString)+1) as NextBreak
    from MyTable
),
SplitNameValue AS
(
    Select 
        FullString, 
        CASE 
            WHEN NextBreak <> 0 THEN 
            SubString (FullString, LocationOfEqualSign, NextBreak-LocationOfEqualSign) 
            ELSE '' END
        as NameValuePair
    From PatternIndex
)
SELECT * 
FROM SplitNameValue

Returns

FullString                                                NameValuePair
--------------------------------------------------------- ---------------------------------------------------------
fkgbokfgbo&name=fokdofkd&okfaos                           name=fokdofkd
okbogkbogkbokgbok                                         
okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos name=fokdofkd

(3 row(s) affected)

edited Jan 28 '11 at 17:12

answered Jan 25 '11 at 12:59

Raj More

47,048
33
131
198

I need to SELECT a field from the DB first.. I cant use hard coded value – Himberjack Jan 25 '11 at 13:00
@oshafran See my first select under the WITH - replace that with your SELECT statement such as `SELECT Youfield as FullString From YourTable` – Raj More Jan 25 '11 at 13:04
Invalid length parameter passed to the LEFT or SUBSTRING function. – Himberjack Jan 25 '11 at 13:11
@oshafran that was happening to you because you did not have a `&name=` terminated by `&`. I modified the query to do the proper thing. – Raj More Jan 25 '11 at 13:16
@Raj More: If the string ends with `name=...` (i.e. no terminating `&`), the substring is not extracted with your query. Although the OP may have no objection to such a case. – Andriy M Jan 28 '11 at 09:38
@Andriy M, I modified the SQL to append an ampersand to account for this – Raj More Jan 28 '11 at 15:25
@Raj More: It works now all right, +1. Only one more thing that I had to fix when I tested it: after your last change the script became broken, because `FullString + '&'` was not aliased. – Andriy M Jan 28 '11 at 16:45

Randy Minder · Answer 2 · 2011-01-25T13:01:30.580

1

This will work I think:

Declare @String varchar(100)

Set @String = 'okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos'

Select SubString(@String,CharIndex('name=',@String),len(@String))

This produces:

'name=fokdofkd&okfaos'

edited Jan 25 '11 at 13:01

answered Jan 25 '11 at 12:54

Randy Minder

47,200
49
204
358

1

length 9999 is not right as there can follow more &name=value – Matten Jan 25 '11 at 12:56
@Matten - How do you know that? That's not indicated in the question. – Randy Minder Jan 25 '11 at 12:57
in you given `@String` follow a `&okfaos` after `&name=fokdofkd`, I don't think `&okfao` belongs to the value revelator wants. – Matten Jan 25 '11 at 12:59
@Matten - you are right, it doesnt belong. only name=fokkofkof – Himberjack Jan 25 '11 at 13:09

score 1 · Answer 3 · answered Jan 25 '11 at 12:56

1

SELECT 'name=' + SUBSTRING(field, CHARINDEX('name=', field), CHARINDEX('&', SUBSTRING(field, CHARINDEX('name=' ,field))))

answered Jan 25 '11 at 12:56

Matten

17,365
2
42
64

score 1 · Answer 4 · answered Jan 25 '11 at 12:57

1

Something along the lines of...

select substring(ColumnName, charindex('name=',ColumnName,1),len(ColumnName))

answered Jan 25 '11 at 12:57

grapefruitmoon

2,988
1
22
26

How to extract substring in SQL Server

4 Answers4

Linked