Thread: between characters

I have written a line to a field that I need to separate out now using sql. The value would be something like "ID:100004 23ksdflk". I need to pull just the 100004 value for part of my query. Any ideas?

Just do a String.Split

I am trying to create a sproc to do this.

What's wrong with SUBSTRING?

SELECT SUBSTRING(fieldname,4,6) FROM tablename

There are a few 'string.split' sprocs published here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

I ended up using the following:

Code:

LEFT(SUBSTRING(col, nullif(PATINDEX('%ID:[^ ]% %', col),0)+3, LEN(col)),6) AS ORDER_NUM,

This finds "ID:" and takes the 6 numbers to the right of it.