CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Nithin
Nithin

Reputation: 1

SQL select to get a string between two Special Characters

I have data like ABC_DEF_123,DEF_GHI_345,GHI_JKL_678 I need to select 123,345,678 (i.e) Between "," and "" How to select between "," and last "" Data is not a static one. Help me out... Thanks in advance

Upvotes: 0

Views: 228

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try below code ,try to wrap below code in function

DECLARE @string NVARCHAR(MAX)='ABC_DEF_123,DEF_GHI_345,GHI_JKL_678'
DECLARE @delimiter CHAR(1)=','
DECLARE @output TABLE(splitdata NVARCHAR(MAX) )
BEGIN 
DECLARE @start INT, @end INT 
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
WHILE @start < LEN(@string) + 1 BEGIN 
    IF @end = 0  
        SET @end = LEN(@string) + 1

    INSERT INTO @output (splitdata)  
    VALUES(SUBSTRING(@string, @start, @end - @start)) 
    SET @start = @end + 1 
    SET @end = CHARINDEX(@delimiter, @string, @start)

END 
--
DECLARE @values NVARCHAR(max)=''
SELECT @values=RIGHT(splitdata,3)+','+@values FROM @output ORDER BY splitdata DESC

SELECT  (SUBSTRING(@values,0,LEN(@values))) 
END

Upvotes: 1

Ramesh
Ramesh

Reputation: 43

Try the below script which may help you

select right('ABC_DEF_123',3) as DataValue

select substring('ABC_DEF_123',patindex('%[0-9]%','ABC_DEF_123'),len('ABC_DEF_123'))

Upvotes: 0

Related Questions