Split column value and select last element in SQL Server

Waghmare
 
on Oct 14, 2021 04:11 AM
378 Views

Hi Team,

I have a data snowflake table column as below.

tbl_Emp

EMP_Key ,Name

121||RAW||231A , Raw

122||STEVE||232B, Steve

I want to fetch only below bold value from column which is after '||' bars.

122||STEVE||232B

So the output i will get 231A for first row and 232B for second row.

It is something like split string.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 14, 2021 04:11 AM

Refer below query.

SQL

DECLARE @Test AS TABLE (EMP_Key VARCHAR(50),Name VARCHAR(20))

INSERT INTO @Test VALUES('121||RAW||231A' , 'Raw')
INSERT INTO @Test VALUES('122||STEVE||232B','Steve')

SELECT SUBSTRING(EMP_Key,(LEN(EMP_Key)-CHARINDEX('||',REVERSE(EMP_Key)))+2,LEN(EMP_Key)) 'EMP_Key'
FROM @Test

Output

EMP_Key

231A

232B