Extract string between chars in SQL Server strings

Question

Can some one kindly suggest ways by which I can extract 0459 and 0460 from a string like (&0459&/&0460&)*100 in a SQL Server table?

I should be able to pull out two strings sandwiched between two pair of ampersands.

Thanks in advance.

the &/& is also a sandwiched string. How would you avoid that ? — t-clausen.dk, Mar 02 '15 at 13:25
you can do it using PatIndex and SubString, once you decide what rules to use. — Tab Alleman, Mar 02 '15 at 15:21

score 1 · Accepted Answer · answered Mar 02 '15 at 12:37

If you are certain there are always two pair of ampersands you may extract the two strings like this

declare @s varchar(max) = '(&0459&/&0460&)*100'

declare @first int = charindex('&', @s)
declare @second int = charindex('&', @s, @first+1)
declare @third int = charindex('&', @s, @second+1)
declare @fourth int = charindex('&', @s, @third+1)

select substring(@s, @first+1, @second-@first-1)
select substring(@s, @third+1, @fourth-@third-1)

score 1 · Answer 2 · answered Mar 02 '15 at 13:29

1

This solution will grap 1st and 3th value between '&'

;WITH CTE AS
(
     SELECT t.c.value('.', 'VARCHAR(2000)') v,
     rn = row_number() over (order by (select 1))-1
     FROM (
         SELECT x = CAST('<t>' + 
               REPLACE('(&0459&/&0460&)*100', '&', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
)
SELECT v FROM CTE
WHERE rn in (1,3)

Result:

0459
0460

answered Mar 02 '15 at 13:29

t-clausen.dk

43,517
12
59
92

Hi, Thanks for your suggestion, I am trying your answer as well, but its a bit more complicated. – VivekDev Mar 03 '15 at 04:55

Extract string between chars in SQL Server strings

2 Answers2