Find the 2end last code in a comma seperated string.

Question

1.00/5 (1 vote)

See more:

In my sql statement i only want to check the 2end last code in the comma seperated string. the amount of codes vary.

In this example i only what to check 'CX' against the code list.

when instr('AX,BX,CX,DX', CODE) > 0

What I have tried:

i have tried to search Google for a solution

Posted 16-Nov-18 0:02am

Jesper Schlütter

Updated 29-Nov-18 18:10pm

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kirthiga S · Answer 1 · 2018-11-29T18:11:00

Solution 5

I hope this will work even the code size vary

Declare @code varchar(20) ='AX,BX,CX,DX,XX'
;with cte as (
select 1 SNO,LEFT(@code,charindex(',',@code)-1)Code,RIGHT(@code,LEN(@code)-charindex(',',@code))RemCode
union all
select SNO+1,LEFT(RemCode,charindex(',',RemCode)-1),RIGHT(RemCode,LEN(RemCode)-charindex(',',RemCode))from cte where @code is Not null and charindex(',',RemCode)>0
)select Code from cte where SNO=(select max(SNO) from cte)

Posted 29-Nov-18 18:11pm

kirthiga S

Comments

CHill60 30-Nov-18 7:35am

Did you know you can use the "Improve solution" link to add information to your original solution? That is better than posting another solution because it makes it clearer that you have rethought the scenario and shows your thought process - it's less confusing for readers.

However, reason for my vote of 4. This is quite an elegant solution when you delve into it. It works for varying numbers of items >= 2 (whereas my solution will actually fail if number of items = 2)

Personally I would have explained that the solution uses a Recursive Common Table expression to generate a sequence or list of most of the component parts in the original string (the last one is omitted because of the check and charindex(',',RemCode)>0. Each item is assigned a number in that CTE which represents its position in the string. You then just select the last item in the sequence to get the desired result.

In other words - you add more value if you explain how to got to a solution rather than just dumping the code

OriginalGriff · Answer 2 · 2018-11-16T00:18:00

First off, SQL string handling is poor at best, so any design which stores CSV data in SQL columns has to be viewed with extreme scepticism - it's probably badly designed and should use a separate table with a foreign key back to the original instead.

There is no "nice" solution, but you can use this: Converting comma separated data in a column to rows for selection[^] Once you have teh data as a temporary table, you can select any item you need from it.

But I'd seriously consider redesigning the DB to get rid of the CSV data - this is only the first of probably many nasty problems it will cause.

MadMyche · Answer 3 · 2018-11-16T01:26:00

What you are going to want to do is to utilize a string splitting function. The easy way to do this is going to use multiple queries; the first to convert the string to rows, and the last to get the specific row.

SQL Server 2016 has this function built in, called STRING_SPLIT
STRING_SPLIT (Transact-SQL) | Microsoft Docs[^]
Other versions of SQL can have similar functions installed, some are better than the now native function. SQL Server Central has quite a few and documentation on testing of these.

This is a very quick and ugly routine that has the basics of what you want. Once understood this should be refactored.

I am leaving it unrefined so that you can see the basics of the operations that are needed which reinforces OriginalGriff's assertion that string functions just aren't SQLs forte.
You would be much better off working in C# or whatever language and converting this to an array, then you can just grab whatever value by index

SQL

DECLARE @ElementCount INT =0
DECLARE @CSV NVARCHAR(1000) = 'AX,BX,CX,DX'
DECLARE @DesiredValue NVARCHAR(100)

DECLARE @SplitTable TABLE(idx INT IDENTITY(1,1) NOT NULL, Element NVARCHAR(100))

INSERT @SplitTable SELECT val FROM STRING_SPLIT(@CSV, ',')
SET @ElementCount = @@RowCount

SELECT @DesiredValue = val FROM @SplitTable WHERE idx = @ElementCount -1 

PRINT @DesiredValue

CHill60 · Answer 4 · 2018-11-28T23:53:00

What solution 3 was trying to do and an option if you have an earlier version of SQL than 2016 (but 2008 or above). Basically reverse the string and remove the (now) first item, returning the new first item reversed (back as it was)...

SQL

Declare @code varchar(20)='AX,BX,CX,DX,XX'
Declare @revCode varchar(20) = REVERSE(@code)
set @revCode  = SUBSTRING(@revCode, CHARINDEX(',',@revCode) + 1, LEN(@revCode))
set @revCode = REVERSE(LEFT(@revCode, CHARINDEX(',', @revCode) - 1))
select @revCode -- DX

But I'm with the others that have suggested doing this in the calling layer not SQL if at all possible.

kirthiga S · Answer 5 · 2018-11-28T23:18:00

Solution 3

Declare @code varchar(20)='AX,BX,CX,DX'
Declare @FirstRemCode varchar(20),@SecondRemCode varchar(20)
select @FirstRemCode=RIGHT(@code,LEN(@code)-charindex(',',@code))
select @SecondRemCode=RIGHT(@FirstRemCode,LEN(@FirstRemCode)-charindex(',',@FirstRemCode))
select LEFT(@SecondRemCode,charindex(',',@SecondRemCode)-1) Code

Posted 28-Nov-18 23:18pm

kirthiga S

Updated 28-Nov-18 23:19pm

v2

Comments

CHill60 29-Nov-18 5:25am

OP Said:the amount of codes vary.If I run your code with @code='AX,BX,CX,DX' then it returns CX as expected, but if I run it with @code = 'AX,BX,CX,DX,XX' it still returns CX instead of DX. In other words, your solution does not work

Find the 2end last code in a comma seperated string.

5 solutions

Solution 5

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0