Unable to print the charindex of a special character of its 4th occurrence.

Question

Unable to print the charindex of a special character of its 4th occurrence.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
November 17, 2015 at 12:13 pm

#399341

Hello,
I am unable to get the 3rd and 4th occurrence of the "_" using charindex. Can anyone please suggest?
Can bring 1st and 2nd occurence position.
example string:
AB_CD_EF_GH_IJ
SELECT CHARINDEX('_', name) a_First_occurence,
CHARINDEX('_', name, (CHARINDEX('_', name)+1)) a_Second_occurrence
Thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

SQL-DBA-01 (11/17/2015)
Hello,
I am unable to get the 3rd and 4th occurrence of the "_" using charindex. Can anyone please suggest?
Can bring 1st and 2nd occurence position.
example string:
AB_CD_EF_GH_IJ
SELECT CHARINDEX('_', name) a_First_occurence,
CHARINDEX('_', name, (CHARINDEX('_', name)+1)) a_Second_occurrence

You've been around long enough to know what they are. Use a Tally "Table". If you're actually trying to split the elements out, use the DelimitedSplit8K function.

--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)

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

Just for fun and practice:

DECLARE @x varchar(100) = 'AB_CD_EF_GH_IJ';

WITH L1 AS (SELECT N=1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))X(x)), -- 10 rows

iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L1 a, L1 b), -- 100 rows

Letters AS

(

SELECT TOP(CONVERT(bigint,DATALENGTH(@x),0)) -- lose the Implicit Conversion in the TOP clause

Occurance = RANK() OVER (ORDER BY N),

Position = N,

Letter = SUBSTRING(@x,CONVERT(int, N, 0),1) -- lose the Implicit Conversion in the filter

FROM iTally

WHERE SUBSTRING(@x,N,1) = '_'

)

SELECT *

FROM Letters

WHERE Occurance IN (3,4); -- get only the 3rd and 4th occurrence

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

What's wrong in this query?

SELECT CHARINDEX('_', name) FirstIndexOf,

--CHARINDEX('_', name)+1,

CHARINDEX('_', name, (CHARINDEX('_', name)+1)) SecondIndexOf,

charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ) ThirdIndexOf,

charindex('_',name,CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) FourthIndexOf,

RTRIM(LTRIM(replace(SUBSTRING(name,charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ),

(charindex('_',name,

CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) - charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ))),'_','')))

as "Max_Value"

,name

FROM sys.databases

Results are coming as is but getting the below error:

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Thanks.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 4

SQL-DBA-01 (11/17/2015)
What's wrong in this query?
SELECT CHARINDEX('_', name) FirstIndexOf,
--CHARINDEX('_', name)+1,
CHARINDEX('_', name, (CHARINDEX('_', name)+1)) SecondIndexOf,
charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ) ThirdIndexOf,
charindex('_',name,CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) FourthIndexOf,
RTRIM(LTRIM(replace(SUBSTRING(name,charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ),
(charindex('_',name,
CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) - charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ))),'_','')))
as "Max_Value"
,name
FROM sys.databases
Results are coming as is but getting the below error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

There are some db names that won't have _s. Try adding this to the query:

WHERE name LIKE '%[_]%[_]%[_]%[_]%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

SQL-DBA-01 (11/17/2015)
What's wrong in this query?
SELECT CHARINDEX('_', name) FirstIndexOf,
--CHARINDEX('_', name)+1,
CHARINDEX('_', name, (CHARINDEX('_', name)+1)) SecondIndexOf,
charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ) ThirdIndexOf,
charindex('_',name,CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) FourthIndexOf,
RTRIM(LTRIM(replace(SUBSTRING(name,charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ),
(charindex('_',name,
CHARINDEX('_',name,(charindex('_', name ,(charindex('_',name ) + 1 )) + 1 )) + 1) - charindex('_',name, CHARINDEX('_',name,(charindex('_',name ) + 1 )) + 1 ))),'_','')))
as "Max_Value"
,name
FROM sys.databases
Results are coming as is but getting the below error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Once you get that working, what are you actually going to do with the output?

--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)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

I wanted to generate databases with auto seq no.

Thanks.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

SQL-DBA-01 (11/18/2015)
I wanted to generate databases with auto seq no.

Ah. Thank you and understood. Before you continue down that path, though, consider how many swear words you might spell out. I strongly recommend incremental alpha and alpha-numeric sequences (with the possible exception of hexadecimal) for that and many other reasons.

--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)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

SQL_Hunt

SSC-Dedicated

Points: 33518

November 18, 2015 at 2:15 pm

#1840841

Thnx ScottPletcher...

Thanks.