SQL How can I return a specific position from a string?

Question

I have the following string:

declare @Request varchar(255)
set @Request= '**URGENT** Apple / Iphone/ Test/Future Resolution/Approved'

Using an SQL query, how do I return just the word 'Apple'?

I've tried something like this but it does not work:

select substring(@Request,CharIndex('** ',@Request),charindex(' / ',@Request))

Thank you so much for your input,

Lori

Answer 1

You adjust what you have to get it correct:

 declare @Request varchar(255)
 set @Request= '**URGENT** Apple / Iphone/ Test/Future Resolution/Approved'


 select substring(@Request,CharIndex('** ',@Request) + 3,charindex(' / ',@Request)-CharIndex('** ',@Request) -3)

Basically you substring needs a start and length so you need to adjust your parameters.

I feel like noting here that one might naturally try to make this more generic by parameterizing the delimiters in someway and then using LEN(first_delim) instead of hardcoding 3 and -3. The gotcha here is that LEN() ignores trailing space as you have in both of these delimiters. If the delims are VARCHAR you can instead use DATALENGTH, but if NVARCHAR you have to divide that result.

I think it is worth pointing out that DATALENGTH by itself doesn't really do since LEN works on NVARCHAR.

SELECT LEN(N'** ') 
      ,LEN(N'** ')
      ,DATALENGTH('** ')
      ,DATALENGTH(N'** ')
      ,DATALENGTH(N'** ')/2