August 10, 2016 at 9:16 am
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
August 10, 2016 at 9:34 am
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
August 10, 2016 at 9:37 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2016 at 12:41 pm
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
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply