I don't need to get after comma in SQL query-CodePudding

I tried many methods and SQL queries. I spent exactly 4 hours on this. I've gotten to a point in what I need but I haven't reached my goal.please help me.

TABLE example:

OperationBO:5003,OP79_ON_KAZAK_YUK,#
OperationBO:5003,OP80_KAZAK_REZISST,#
OperationBO:5003,OP81_KORR_MONT,#
OperationBO:5003,OP82_ON_BETLI_MONTU,#
OperationBO:5003,OP83_EXPANDIBILI_MONTU,#

expected result:

OP79_ON_KAZAK_YUK
OP80_KAZAK_REZISST
OP81_KORR_MONT
OP82_ON_BETLI_MONTU
OP83_EXPANDIBILI_MONTU

where i can come:

the query i used: MY QUERY:

SELECT SUBSTRING(OPERATION_BO, 18) ss
FROM SFC_STEP

MY QUERY RESULT:

OP79_ON_KAZAK_YUK,#
OP80_KAZAK_REZISST,#
OP81_KORR_MONT,#
OP82_ON_BETLI_MONTU,#
OP83_EXPANDIBILI_MONTU,#

CodePudding user response：

If it's always ,# at the end you could solve it by adding a REPLACE

declare @test NVARCHAR(max)='OperationBO:5003,OP79_ON_KAZAK_YUK,#'
SELECT REPLACE(SUBSTRING(@test,CHARINDEX(',',@test) 1,LEN(@test)),',#','')

Returns OP79_ON_KAZAK_YUK

CodePudding user response：

You can try the following queries:

If the starting values are always in the format OperationBO:5003,

Try,

SELECT substring_index(substring_index(OPERATION_BO, '5003,', -1),
                       ',', 1) ss
FROM SFC_STEP;

This take in consideration that before the first comma is 5003 expected.

Another approach:

SELECT substring_index(substring_index(OPERATION_BO, ',#', 1),',', -1) ss
FROM SFC_STEP;

This take in consideration that after the second comma is # always expected.

Note, this work only for two commas in the string

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=06b29bd64c5dde042b3010f630813c8d