Using Charindex

Question

Post reply

Using Charindex

sam 55243

SSCrazy

Points: 2697
More actions
August 10, 2016 at 9:16 am

#150498

Hi All,
I'm using stored procedure where i'm getting role Id in comma separated values and i need to check for specific role id in that. Im using charindex for this and wanted to know if this is best possible solution or is there any alternative
-- THIS IS FOR EXAMPLE
DECLARE @TradeRoles VARCHAR(100)
SET @TradeRoles = '1,2,3,4,5'
-- If the user has role of 1&4 only then Proceed for furhter processing
IF CHARINDEX('1',@TradeRoles) > 0 AND CHARINDEX('4', @TradeRoles) > 0
BEGIN
-- ADDITIONAL CODE
END
Please suggest

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76997 More actions · Answer 1

No, it's not. Consider the following example:

-- THIS IS FOR EXAMPLE

DECLARE @TradeRoles VARCHAR(100)

SET @TradeRoles = '2,3,5,14'

-- If the user has role of 1&4 only then Proceed for furhter processing

IF CHARINDEX('1',@TradeRoles) > 0 AND CHARINDEX('4', @TradeRoles) > 0

BEGIN

-- ADDITIONAL CODE

END

The best option is not to use delimited strings. The second best option is to use a string splitter like the DelimitedSplit8K mention in Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url].

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

You could use a splitter. I tend to use this[/url] one, because it's been highly tested and optimised.

DECLARE @TradeRoles VARCHAR(100);

SET @TradeRoles = '1,2,3,4,5';

IF (SELECT COUNT(1)

FROM dbo.udfDelimitedSplit8K(@TradeRoles, ',')

WHERE Item IN ('1', '4')

) = 2

BEGIN

SELECT 'Found';

END;

ELSE

SELECT 'Not found';

Note, however, that the comment and the code are not equivalent.

The code succeeds if both 1 and 4 are found in the string, regardless of any other items in the string.

However, the comment suggests that the test should be successful only if 1 and 4 are the only items in the string.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

sam 55243 (8/10/2016)
Hi All,
I'm using stored procedure where i'm getting role Id in comma separated values and i need to check for specific role id in that. Im using charindex for this and wanted to know if this is best possible solution or is there any alternative
-- THIS IS FOR EXAMPLE
DECLARE @TradeRoles VARCHAR(100)
SET @TradeRoles = '1,2,3,4,5'
-- If the user has role of 1&4 only then Proceed for furhter processing
IF CHARINDEX('1',@TradeRoles) > 0 AND CHARINDEX('4', @TradeRoles) > 0
BEGIN
-- ADDITIONAL CODE
END
Please suggest

Nothing wrong with the splitter approach but you can resolve this just using CHARINDEX. The problem with the way you're doing it is that you can return false positives. Consider this code:

DECLARE @TradeRoles VARCHAR(100)

SET @TradeRoles = '100,2,3,4,5'

SELECT CHARINDEX('1',@TradeRoles);

This would work just fine:

IF CHARINDEX(',1,', ','+@TradeRoles+',') > 0 AND CHARINDEX(',4,', ','+@TradeRoles+',') > 0

BEGIN

PRINT 'yep!'

END

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001