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

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 10, 2016 at 9:34 am

  #1894825

  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: 246960

  More actions

  August 10, 2016 at 9:37 am

  #1894826

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  August 10, 2016 at 12:41 pm

  #1894882

  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