CODEWITHSUNDEEP
sql-serverstringt-sqlsubstringcharindex
Michael
Michael

Reputation: 2657

Extract right of 2nd occurrence of character in string

I'm using SQL Server 2014.

I have a table with a field called 'addr' that contains string values like the below:

ROOM 1 101 My Street; My Town
ROOM 2 101 My Street; My Town
ROOM A 123 My Crescent; My Old Town
ROOM A 12a My Avenue; My Very Old Town

I need to do two things:

  1. Extract the numbers between the 2nd ' ' and 3rd ' ' resulting in:
101
101
123
12a
  1. Extract the text after the 3rd ' ' and up to the first ';' resulting in:
My Street
My Street
My Crescent
My Avenue

How do I do this?

I've tried:

SELECT CAST('<x>' + REPLACE(addr, ' ', '</x><x>') + '</x>' AS XML).value('/x[1]', 'varchar(200)') from mytable;

But this results in 'ROOM'.

UPDATE:

To extract the numbers between 2nd and 3rd I have used the below:

SELECT CAST('<x>' + REPLACE(addr, ' ', '</x><x>') + '</x>' AS XML).value('/x[3]', 'varchar(200)') from MyTable;

How do I extract from the 3rd ' ' and first ';' though?

Upvotes: 0

Views: 3325

Answers (2)

casenonsensitive
casenonsensitive

Reputation: 950

You can program it in a very rigid way like this, if your requirements won't change too much over time:

with t as  (
select 'ROOM 1 101 My Street; My Town' a union all
select 'ROOM 2 101 My Street; My Town' a union all
select 'ROOM A 123 My Crescent; My Old Town' a union all
select 'ROOM A 12a My Avenue; My Very Old Town' a )
, t2 as (
select a, PATINDEX('% %', a) pi1, 
          PATINDEX('% %', SUBSTRING(a, 1 + nullif(PATINDEX('% %', a), 0), LEN(a))) pi2, 
          PATINDEX('% %', SUBSTRING(a, 1 + PATINDEX('% %', SUBSTRING(a, 1 + nullif(PATINDEX('% %', a), 0), LEN(a))) + nullif(PATINDEX('% %', a), 0), LEN(a))) pi3,
          PATINDEX('%;%', SUBSTRING(a, 1 + PATINDEX('% %', SUBSTRING(a, 1 + PATINDEX('% %', SUBSTRING(a, 1 + nullif(PATINDEX('% %', a), 0), LEN(a))) + nullif(PATINDEX('% %', a), 0), LEN(a))) + PATINDEX('% %', SUBSTRING(a, 1 + nullif(PATINDEX('% %', a), 0), LEN(a))) + nullif(PATINDEX('% %', a), 0), LEN(a))) pi4
  from t
)
select a, SUBSTRING(a, pi1, pi2) s1, SUBSTRING(a, pi1 + pi2, pi3) s2, SUBSTRING(a, pi1 + pi2 + pi3, pi4) s3, SUBSTRING(a, pi1 + pi2 + pi3 + pi4, len(a)) s4
 from t2

And it returns: enter image description here

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

Answered by Joe J in comments:

If you want it all in one select statement, you could use a horrid combination of CHARINDEX and SUBSTRING. Something like this

select SUBSTRING(addr, charindex(' ', addr, charindex(' ', addr, 1) + 1) + 1,charindex(' ', addr, charindex(' ', addr, charindex(' ', addr, 1) + 1) + 1) - charindex(' ', addr, charindex(' ', addr, 1) + 1) -1)

will get your numbers between the 2nd and 3rd ' '.

Thanks @JoeJ - how do I modify it to say from 3rd ' ' to first ';' ? – Michael

@Michael,

select SUBSTRING(addr,charindex(' ', addr, charindex(' ', addr, charindex(' ', addr, 1) + 1) + 1) + 1,charindex(';', addr, 1) - charindex(' ', addr, charindex(' ', addr, charindex(' ', addr, 1) + 1) + 1) -1) will do between the third ' ' and the first ';'.

Like I said, it's pretty horrid though, and is strictly reliant on the spaces etc. being exactly in the same place in every record.

Upvotes: 2

Related Questions