search text within for string

Question

search text within for string

SQL_Kills

Hall of Fame

Points: 3154
More actions
October 24, 2013 at 8:55 am

#279070

Hi,
I have the following query I want a new field to be added that will just pick the character
in between the status=" ", so the fields newText1, newText2, newText3 is what i would like the value to be show as:
Select
'<OrdRsp Currency="GBP" Status="8"><Head><MessageCrea' as Text1,
'<OrdRsp Currency="GBP" Status="85"md><Head><MessageCrea' as Text2,
'<OrdRsp Currency="GBP" Status="833"><Head><MessageCrea' as Text3,
8 as newText1,
85 as newText2,
833 as newText3
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

I'm not the greatest at string manipulation, but here you go:

select substring(col1,CHARINDEX('Status="',Col1,1)+8, (charindex('"',col1,CHARINDEX('Status="',Col1,1)+8))-(CHARINDEX('Status="',Col1,1)+8))

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I had put together my own version but saw Keith posted one too.

I created some ddl to work with which I will post. I converted Keith's to use the same ddl. They both do the same thing just a different way of getting there. Keith used fewer function than I did so I would use his over mine if I had to choose.

create table #Something

(

SomeValue varchar(100)

)

insert #Something

Select '<OrdRsp Currency="GBP" Status="8"><Head><MessageCrea' union all

select '<OrdRsp Currency="GBP" Status="85"md><Head><MessageCrea' union all

select '<OrdRsp Currency="GBP" Status="833"><Head><MessageCrea'

select

left(SUBSTRING(SomeValue,charindex('Status="',SomeValue,0)+8,LEN(SomeValue)),charindex('"',SUBSTRING(SomeValue,charindex('Status="',SomeValue,0)+8,LEN(SomeValue)))-1),

substring(SomeValue,CHARINDEX('Status="',SomeValue,1)+8,(charindex('"',SomeValue,CHARINDEX('Status="',SomeValue,1)+8))-(CHARINDEX('Status="',SomeValue,1)+8))

from #Something

drop table #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I wanted to include the XML technique for this because Text1,2 & 3 all look like truncated XML (are those the actual values or did you truncate the values for readability?).

If it were well-formed xml (note the changes I made in the example below) then you could do something like this:

SELECT'<OrdRsp Currency="GBP" Status="8"><Head><MessageCreated/></Head></OrdRsp>' as Text1,

'<OrdRsp Currency="GBP" Status="85"><Head><MessageCreated/></Head></OrdRsp>' as Text2,

'<OrdRsp Currency="GBP" Status="833"><Head><MessageCreated/></Head></OrdRsp>' as Text3

INTO #xx;

WITH makexml(Text1,Text2,Text3) AS

(SELECT CAST(Text1 AS xml), CAST(Text2 AS xml), CAST(Text3 AS xml) FROM #xx)

SELECTText1.value('(//@Status)[1]', 'varchar(10)') AS newText1,

Text2.value('(//@Status)[1]', 'varchar(10)') AS newText2,

Text3.value('(//@Status)[1]', 'varchar(10)') AS newText3

FROM makexml

With the sample code you provided you could go the same route with a small tweak like so:

IF OBJECT_ID('tempdb..#x') IS NOT NULL DROP TABLE #x;

SELECT'<OrdRsp Currency="GBP" Status="8"><Head><MessageCrea' as Text1,

'<OrdRsp Currency="GBP" Status="85"><Head><MessageCrea' as Text2,

'<OrdRsp Currency="GBP" Status="833"><Head><MessageCrea' as Text3

INTO #x;

WITH makexml AS

(

SELECTCAST(REPLACE(LEFT(Text1,CHARINDEX('>',text1)),'>','/>') AS xml) AS Text1,

CAST(REPLACE(LEFT(Text2,CHARINDEX('>',text2)),'>','/>') AS xml) AS Text2,

CAST(REPLACE(LEFT(Text3,CHARINDEX('>',text3)),'>','/>') AS xml) AS Text3

FROM #x

)

SELECTText1.value('(//@Status)[1]', 'varchar(10)') AS newText1,

Text2.value('(//OrdRsp/@Status)[1]', 'varchar(10)') AS newText2,

Text3.value('(//OrdRsp/@Status)[1]', 'varchar(10)') AS newText3

FROM makexml;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001