Get 2nd Occurence

Question

Get 2nd Occurence

TriggerMe

SSChampion

Points: 11752
More actions
January 27, 2009 at 5:43 pm

#127602

I have the following code that retrieves tha last characters '200BB' The value is variable so it could have '100AAA'.
SO i did it by determining the 2nd occurence of the SPACE and retrieving the remaining text with the RIGHT Function.
Question, is there a simpler way to do this as although my method works it appears to be clunky (lengthy) and i get the feeling that it should be simpler than the code i have written.
DECLARE @DeptName VARCHAR(30), @Start INT, @CostCode VARCHAR(10), @DeptName2 VARCHAR(30), @CostCode2 VARCHAR(10),
@Start2 INT
SET @DeptName = 'PM274 P-GM 200BB'
--SET @DeptName = 'C358 101 100AAA'
SET @Start = (SELECT PATINDEX ( '% %' , @DeptName))
SET @CostCode = LEFT(@DeptName, @Start)
SET @DeptName2 = REPLACE(@DeptName, @CostCode, '')
SELECT @Start, @CostCode, @DeptName2
SET @Start2 = (SELECT PATINDEX ( '% %' , @DeptName2))
SET @CostCode2 = RIGHT(@DeptName2, @Start2)
SELECT @Start, @CostCode, @DeptName2, @Start2, @CostCode2
Kindest Regards,

Viewing 13 posts - 1 through 12 (of 12 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Here's another way to do it. (Not saying it's any better) If you're doing this on a whole column, you're likely going to want to look into a tally table solution.

DECLARE @DeptName varchar(25),

@S1int,

@S2int

SET @DeptName = 'PM274 P-GM 200BB'

SET @S1 = CHARINDEX(' ', @DeptName) -- First Space Pos

SET @S2 = LEN(@DeptName) - CHARINDEX(' ', REVERSE(@DeptName)) -- Second Space Pos

SELECT LEFT(@DeptName,@S1), SUBSTRING(@DeptName, @S1, (@S2 - @S1)+1), RIGHT(@DeptName, LEN(@DeptName) - @S2)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

What is the maximum number of spaces that could occur and are periods ever going to appear in the data?

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

TriggerMe SSChampion Points: 11752 More actions · Answer 3

Jeff,

There will only be 2 spaces and no periods.

Kindest Regards,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Trigger (1/27/2009)
Jeff,
There will only be 2 spaces and no periods.

In that case... I'm not sure it's any faster (haven't done the ol' million row test on this one), but it is pretty simple... 😛

SELECT PARSENAME(REPLACE('PM274 P-GM 200BB',' ','.'),1)

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

TriggerMe SSChampion Points: 11752 More actions · Answer 5

Jeff,

This is a great bit of T-SQL magic!

I understand that the REPLACE Function is replacing the ' ' with a '.'

However how on earth does the PARSENAME Function retrieve the '200BB' only from the entire string?

I'm confused and I'm also a Database Professional that likes to understand what's happening under the hood for my own sanity!

Can you kindly explain how this works?

Kindest Regards,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

No problem... especially since the write-up in BOL leaves a lot for the reader to discover.

PARSENAME was designed to parse the 1 to 4 part object names (usually tables or views) in SQL Server. Those are in the form of server.database.schema.objectname. See the "dots"? That's why I did the replace of spaces with periods... the delimiter for PARSENAME is a period. The code is, apparently written to return the objectname as piece #1 all the time (that much is in BOL) even if the other 1, 2, or 3 parts are missing. Schema would be #2, Database #3, and Server #4. It also works well on parsing something with a similar form... IP addresses.

I just tricked PARSENAME into thinking the 3 part partnumbers were 3 part objectnames by replacing the spaces with periods.

That's also why I asked what the max number of spaces was going to be... if it were more than 3, PARSENAME wouldn't have done the deed and I'd have had to do something a little more radical... probably with a Tally-splitter.

Did that answer your question?

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

TriggerMe SSChampion Points: 11752 More actions · Answer 7

Jeff,

Yes it explains it and well done. I did realise in the end that you were tricking PARSENAME to do the job.

Your solution was a solution thinking outside the normal thoughts of SQL Development and i have been enlightened with what you have shown me.

Thanks so much.

Kindest Regards,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Absolutely my pleasure, Trigger. Heh... thinking out side the box... a phrase I coined goes as follows...

[font="Arial Black"]Before you can think outside the box, you must first realize... you're in a box. [/font] 😛

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

smeet.sinha Mr or Mrs. 500 Points: 577 More actions · Answer 9

smeet.sinha

Mr or Mrs. 500

Points: 577

January 28, 2009 at 11:16 pm

#934403

U r exceptional Jeff

no more comments ........

TriggerMe SSChampion Points: 11752 More actions · Answer 10

Well said Jeff!

I will remember this saying!

Kindest Regards,

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

smeet.sinha (1/28/2009)
U r exceptional Jeff
no more comments ........

Thanks for the thoughtful comment, Smeet...

By the way, did you ever find a work around concerning executing dynamic SQL in a 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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Trigger (1/29/2009)
Well said Jeff!
I will remember this saying!

Heh... thanks Trigger... I've got a bunch more, but having been in the Navy, most of them aren't suitable to publish here. 😛

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