how to find index using patindex of comma or semicolon seprated str.for sql 2012

Question

Post reply

how to find index using patindex of comma or semicolon seprated str.for sql 2012

rajemessage 14195

Hall of Fame

Points: 3299
More actions
December 21, 2021 at 5:26 am
Go to Answer
#3965762

hi,
--the string @s-2 has comma seprated text , some times it is seprated by semicolon also.(so i need to put or in patindex)
-- i have to find out first text "R1" to do that i need to find out the index of first occurence of comma or semicolan so that i can use substring to get the "R1" ( primary task is to get the "R1")
Qt1) so please tell me how to put "or" in patindex. or any other good method would be helpfull
following is what i was trying. i have also tried to get comma's index. then used the input to get if there is any smicolon. but it needs patindex to be used twoice.
DECLARE @s-2 VARCHAR(MAX)= NULL
SELECT PATINDEX('%[^A-Za-z0-9-())![]]%','sd(())[-s!;dfd')
Q2) how to escape [] in side [] in patindex.

DesNorton

SSC-Insane

Points: 24286

December 21, 2021 at 7:31 am

Answer

#3965809

rajemessage 14195 wrote:

i have rephrased the question
DECLARE @s-2 VARCHAR(MAX)= 'r1,r2' SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s)
DECLARE @S1 VARCHAR(MAX)= 'X1;r2' SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s1)
i need to get first text string before first comma or semicolan ex "r1" and "x1" respectivily . which is seprated by comma or simicolon .

DECLARE @S VARCHAR(MAX)= 'r1,r2';
DECLARE @S1 VARCHAR(MAX)= 'X1;r2';
DECLARE @S2 VARCHAR(MAX)= 'A5'; -- WHAT sould happen here - Update the CASE statement as needed

WITH cteData AS (
  SELECT MyString = @S
  UNION ALL
  SELECT MyString = @S1
  UNION ALL
  SELECT MyString = @S2
)
SELECT  cte.MyString
      , ExtractedString = CASE WHEN ISNULL(cs.iPos, 0) > 0
                               THEN LEFT(cte.MyString, cs.iPos -1)
                               ELSE NULL
                          END
FROM cteData AS cte
OUTER APPLY (SELECT PATINDEX('%[,;]%', cte.MyString)) AS cs(iPos);

How to post data/code on a forum to get the best help.

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

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

rajemessage 14195 Hall of Fame Points: 3299 More actions · Answer 1

i have rephrased the question

DECLARE @s-2 VARCHAR(MAX)= 'r1,r2'

SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s)

DECLARE @S1 VARCHAR(MAX)= 'X1;r2'

SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s1)

i need to get first text string before first comma or semicolan ex "r1" and "x1" respectivily . which is seprated by comma or simicolon .

Jeff Moden SSC Guru Points: 1003909 More actions · Answer 2

A minor optimization on DesNorton's good code would be to remove the ISNULL from the CASE WHEN because a NULL can't be greater than 0 (nor equal to or less than or any other type of comparison).

The ELSE NULL can be removed, as well, but it makes it real obvious what happens and doesn't cost any extra clock cycles.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)