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.
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