Select subset of delimited string field

Question

My table has a field with data formatted like this:

Term 1~Term 2~Term 3~Term 4~Term 5~Term 6~

All non-blank values contain 6 tilde-separated strings, which may be several words long.

I need to extract the last 2 substrings from this field as part of a query.
I'm not interested in splitting the data into multiple records, and I don't have permissions to create a stored procedure.

Thanks in advance for any advice.

Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Aug 09 '18 at 05:14

James G · Accepted Answer · 2018-08-10T19:49:28.963

1

DECLARE @Term VARCHAR(100)

SELECT @Term = 'abc~def~ghi~jkl~mno~pqr~'

SELECT RIGHT(@Term,  CHARINDEX('~',REVERSE(@Term),CHARINDEX('~',REVERSE(@Term),2)+1)-1)

That will give the last two terms with ~ intact. Note you can wrap REPLACE() around that to put something other than the tilde in there.

edited Aug 10 '18 at 19:49

answered Aug 08 '18 at 21:25

James G

118
11

Harry · Answer 2 · 2018-08-08T21:59:59.480

another way to do is this.. use string_split (in 2016) or an equivalent UDF that can be found elsewhere.. to split the string

    declare @term varchar(100) = 'abc~def~ghi~jkl~mno~pqr~'
; with mycte as (
select
 value as string_value
, row_number() over (order by (select  1000) )as row_num 
from string_split(@term,'~'))


select top 2 string_value 
 from mycte
 where string_value<>''

 order by row_num desc

Select subset of delimited string field

2 Answers2