Can someone help with TSQL string search extract?

Question

I would like to extract Tow (Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat|) information from the string below: 

       8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat|

I would like to be able to use the following code if possible: TableName:   policies    Field is: Name

center][/center],    Declare
center][/center],       @data varchar(200),
center][/center],       @position integer

center][/center],      set @data =
center][/center],     set @position =

Select(right(p.Name, len(@data) , @position– (PathIndex(‘%[0-9}%’, @data)-1))
From policies p

Any suggestion would be greatly appreciated.

Regards,
leonie6214

---

leonie6214

Answer 1

Try -

DECLARE @tab TABLE(Txt VARCHAR(MAX))

INSERT INTO @tab 
VALUES('8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat|')

SELECT * FROM @tab

SELECT Txt AS Original,Final.Res AS Final
FROM @tab
CROSS APPLY(SELECT CHARINDEX('|',Txt)AS pos1) AS p1
CROSS APPLY(SELECT SUBSTRING(Txt,p1.pos1+1,LEN(Txt)) AS Sub1) AS Sub
CROSS APPLY(SELECT CHARINDEX('|',LTRIM(Sub.Sub1),2) AS pos2) AS p2
CROSS APPLY(SELECT SUBSTRING(Sub.Sub1,1,p2.pos2+1) AS Res) Final

Output -

 |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat|

---

Narsimha

Answer 2

Hi Narimha,

Thank you for the solution.  It is working fine;  I selected the values from the name field.

How do I search and extract additional info from the same txt field?  I also need to extract the three items below:

'10AM-4PM Mon-Sat $3 60 | Tow: 7AM-10AM Mon-Fri, 4PM-6PM Mon-Fri | Clean: 2AM-5AM Sun-Sat | PrePay:  | ParkNoPay: '

1.       Clean:
2.      Prepay:
3.    ParkNoPay:

Thanks again,

Can I achieve this with union?  Note:  Each record is a variable length string.  See example below:

DECLARE

@tab TABLE(Txt VARCHAR(MAX))

INSERT

INTO @tab(Txt)

select

p.Name  from policies p

SELECT

  Tow.Res AS Tow

FROM

@tab

CROSS

APPLY(SELECT CHARINDEX('|',Txt)AS pos1) AS p1

CROSS

APPLY(SELECT SUBSTRING(Txt,p1.pos1+1,LEN(Txt)) AS Sub1) AS Sub

CROSS

APPLY(SELECT CHARINDEX('|',LTRIM(Sub.Sub1),2) AS pos2) AS p2

CROSS

APPLY(SELECT SUBSTRING(Sub.Sub1,1,p2.pos2+1) AS Res) Tow

Union

all

SELECT

  clean.Res AS clean

FROM

@tab

CROSS

APPLY(SELECT CHARINDEX('|',Txt)AS pos1) AS p1

CROSS

APPLY(SELECT SUBSTRING(Txt,p1.pos1+1,LEN(Txt)) AS Sub1) AS Sub

CROSS

APPLY(SELECT CHARINDEX('|',LTRIM(Sub.Sub1),2) AS pos2) AS p2

CROSS

APPLY(SELECT SUBSTRING(Sub.Sub1,1,p2.pos2 +1) AS Res) clean

---

leonie6214

Answer 3

Hi

Try the code below. As long as you fill in the values for Clean, Tow with values in the string, it will return values.

DECLARE @A NVARCHAR(300) = '8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat|'

SELECT	SUBSTRING(@A, CHARINDEX('Tow', @A), CHARINDEX('|', @A, CHARINDEX('Tow', @A)) - CHARINDEX('Tow', @A))
	   ,SUBSTRING(@A, CHARINDEX('Clean', @A), CHARINDEX('|', @A, CHARINDEX('Clean', @A)) - CHARINDEX('Clean', @A))

Answer 4

Try this:

DECLARE @Text VARCHAR(max) 
Set @Text= '10AM-4PM Mon-Sat $3 60 | Tow: 7AM-10AM Mon-Fri, 4PM-6PM Mon-Fri | Clean: 2AM-5AM Sun-Sat | PrePay:  | ParkNoPay: '

SELECT	SUBSTRING(@Text, CHARINDEX('Tow', @Text), CHARINDEX('Clean', @Text)-CHARINDEX('Tow', @Text)- 2) Tow
		,SUBSTRING(@Text, CHARINDEX('Clean', @Text), CHARINDEX('PrePay', @Text)-CHARINDEX('Clean', @Text)- 2) Clean
		,SUBSTRING(@Text, CHARINDEX('PrePay', @Text), CHARINDEX('ParkNoPay', @Text)-CHARINDEX('PrePay', @Text)- 2) PrePay
		,SUBSTRING(@Text, CHARINDEX('ParkNoPay', @Text), len(@Text) - CHARINDEX('ParkNoPay', @Text) + 1) ParkNoPay

Regards,Eshwar.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

Hi ,

Another approach can be to split your string by delimiter ('|') and create rows.  check below code for more details. I have split out strings to rows.. through Xquery .

Declare @policies table (id int identity(1,1),information nvarchar(max))

insert into @policies
select '8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat| ' 
union all 
select '6Am to 4PM Mon-Fri| |Tow: 1PM to 6PM Mon-Fri, 2AM -9PM Fri-Sat| Clean: 7AM to 9PM Mon-Fri, 2AM -5AM Fri-Sat| '

select * from @policies

Declare @information table (id int,strings nvarchar(max))

insert into @information
select x.ID, rtrim(ltrim(a.b.value('.','nvarchar(1000)')))
from  
( select ID, CAST( '<Rows><Row>'+ REPLACE(p.information,'|','</Row><Row>') + '</Row></Rows>'  as XML) as Info from @policies p ) x
cross apply Info.nodes('Rows/Row') a(b)

select * from @information
select * from @information where strings like 'Tow%' 

you can use temp table to strore splitted strings and query that. Wrap your code in a Sp and use that..

Thanks,

Saurabh

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 6

DECLARE @Text NVARCHAR(200)
SET @Text='8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat|'
SELECT SUBSTRING (@Text,CHARINDEX('Tow',@Text),len('Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat'))

Answer 7

Thank you all for your help.

Hi Eshwar,

With your solution, how do I get the values from the temp table instead of the hardcoded text below:?

Set @Text= '10AM-4PM Mon-Sat $3 60 | Tow: 7AM-10AM Mon-Fri, 4PM-6PM Mon-Fri | Clean: 2AM-5AM Sun-Sat | PrePay:  | ParkNoPay: '

See  below:

DECLARE

@Text VARCHAR(MAX)

DECLARE

@table TABLE(Txt VARCHAR (MAX))

INSERT

INTO @table(Txt)

select

p.Name  from policies p

select

SUBSTRING

(@Text, CHARINDEX('Tow', @Text), CHARINDEX('Clean', @Text)-CHARINDEX('Tow', @Text)- 2) Tow

,SUBSTRING(@Text, CHARINDEX('Clean', @Text), CHARINDEX('PrePay', @Text)-CHARINDEX('Clean', @Text)- 2) Clean

,SUBSTRING(@Text, CHARINDEX('PrePay', @Text), CHARINDEX('ParkNoPay', @Text)-CHARINDEX('PrePay', @Text)- 2) PrePay

,SUBSTRING(@Text, CHARINDEX('ParkNoPay', @Text), len(@Text) - CHARINDEX('ParkNoPay', @Text) + 1) ParkNoPay

---

leonie6214

Answer 8

Try this:

DECLARE @Text VARCHAR(MAX)
DECLARE @table TABLE(Txt VARCHAR (MAX))
INSERT
INTO @table(Txt)
--Values('10AM-4PM Mon-Sat $3 60 | Tow: 7AM-10AM Mon-Fri, 4PM-6PM Mon-Fri | Clean: 2AM-5AM Sun-Sat | PrePay:  | ParkNoPay: ')
select
p.Name  from policies p
select SUBSTRING(Txt, CHARINDEX('Tow', Txt), CHARINDEX('Clean', Txt)-CHARINDEX('Tow', Txt)- 2) Tow
,SUBSTRING(Txt, CHARINDEX('Clean', Txt), CHARINDEX('PrePay', Txt)-CHARINDEX('Clean', Txt)- 2) Clean
,SUBSTRING(Txt, CHARINDEX('PrePay', Txt), CHARINDEX('ParkNoPay', Txt)-CHARINDEX('PrePay', Txt)- 2) PrePay
,SUBSTRING(Txt, CHARINDEX('ParkNoPay', Txt), len(Txt) - CHARINDEX('ParkNoPay', Txt) + 1) ParkNoPay 
From @table

Regards,Eshwar.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 9

Take a look at the last solution (XML solution) in that blog post

Passing multiple ranges to stored procedure

Scroll down to the bottom of this blog.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog