Substring and CharIndex

Transact-SQL
,
1

Hi

My First post here.!

I would like to get the String which is between the Nth Hypens of the given string.

Ex: 123_234_234_345_34A_456
The hypen count may vary, but I would like to get exactly the string within the 4th and 5th Hypen i.e as per example we want 34A .! Will be helpful if someone can guide me to achieve this.! Trying to achieve it in SQL Server.

Regards,
BalaV

2

Use a string splitter such as from here and pick the fourth value from the result of the split.

An alternative, even though it looks a little ugly is described here If you were to use that, it would be like this:

DECLARE @x VARCHAR(64) = '123_234_234_345_34A_456'

SELECT
	[Part_5] = LEFT(e,CHARINDEX('_',e+'_')-1)
FROM
	( VALUES (@x) ) A(a)
	CROSS APPLY (VALUES (STUFF(a,1,CHARINDEX('_',a+'_'),''))) B(b)
	CROSS APPLY (VALUES (STUFF(b,1,CHARINDEX('_',b+'_'),''))) C(c)
	CROSS APPLY (VALUES (STUFF(c,1,CHARINDEX('_',c+'_'),''))) D(d)
	CROSS APPLY (VALUES (STUFF(d,1,CHARINDEX('_',d+'_'),''))) E(e);
1 Like
3

Here's an alternative approach using an in-line tally table:

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT SUBSTRING(@x, MAX(CASE WHEN row_num = 4 THEN number END) + 1, 
    MAX(CASE WHEN row_num = 5 THEN number END) - 
    MAX(CASE WHEN row_num = 4 THEN number END) - 1) AS value
FROM (
    SELECT TOP (5) t.number, ROW_NUMBER() OVER(ORDER BY t.number) AS row_num
    FROM cteTally10K t
    WHERE t.number < LEN(@x) AND SUBSTRING(@x, t.number, 1) = '_'
) AS derived
1 Like