SQL extracting text between characters

Question

I am trying to extract a string between two characters. The length could change for any of the numbers. The values I am trying to extract is only e.g. 0001A, 0002BB, 0003C etc.

Using select SUBSTRING(ordtxt,7,4) as ordtxt, I would be able to only extract x amount of characters from the left which isn't really something I am looking for.

100/0/0001A/001
101/000/0002BB/001
102/00/0003C/0001

Thank you for any help.

This [link]( https://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server) might help — ywwy, Feb 27 '19 at 15:52

score 6 · Accepted Answer · edited Feb 27 '19 at 16:40

6

You could use a simple XML approach, which would isolate values from a delimited string

 SELECT
   convert(xml,'<x>'+replace(YourColumn,'/','</x><x>')+'</x>').value('/x[3]','varchar(100)')
 FROM YourTable

The solution breaks down the string into positions denoted by a numeric index, simply select the required element you need.

For example if you need 0001, 0002, 0003 then use a position of 3 in the function above.

The syntax is based on SQL Server

edited Feb 27 '19 at 16:40

forpas

145,388
9
31
69

answered Feb 27 '19 at 15:55

jimmy8ball

716
5
15

I wouldn't know how to use XML within Ms SQL – VQB Feb 27 '19 at 15:59
you just include it in the select statement – jimmy8ball Feb 27 '19 at 15:59
Thanks jimmy. I attempted your updated select statement but I do get an error for the equal sign Incorrect syntax near '='. – VQB Feb 27 '19 at 16:07
@V.Bach remove `YourColumn =` – forpas Feb 27 '19 at 16:15
1

oh this worked out great. Thanks for the help jimmy & forpas! – VQB Feb 27 '19 at 16:26

SQL extracting text between characters

1 Answers1