String Manipulation/Character Removal

Question

String Manipulation/Character Removal

Jody Claggett-376930

Ten Centuries

Points: 1152
More actions
February 18, 2011 at 1:26 pm

#235276

Hi,
I need manipulating strings and removing patterns in those strings.
I have 3 examples of strings that that are variable in length. Below is how each one looks currently (BEFORE) and how I want the string to look afterwards (AFTER).
BEFORE: IA_JCONANT_1-3JVXWD
AFTER: JCONANT
BEFORE: IA_JAMESF_1-6YHSAT
AFTER: JAMESF
BEFORE: IA_JOOWEN_1-F3D9NN
AFTER: JOOWEN
I can removing the beginning pattern "IA_" easily. However, I am having trouble with how to identify all of the characters from the second underscore and removing all of those.
Can someone help? I look through the forums, but couldn't find a specific answer for this.
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Jody Claggett-376930 (2/18/2011)
Hi,
I need manipulating strings and removing patterns in those strings.
I have 3 examples of strings that that are variable in length. Below is how each one looks currently (BEFORE) and how I want the string to look afterwards (AFTER).
BEFORE: IA_JCONANT_1-3JVXWD
AFTER: JCONANT
BEFORE: IA_JAMESF_1-6YHSAT
AFTER: JAMESF
BEFORE: IA_JOOWEN_1-F3D9NN
AFTER: JOOWEN
I can removing the beginning pattern "IA_" easily. However, I am having trouble with how to identify all of the characters from the second underscore and removing all of those.
Can someone help? I look through the forums, but couldn't find a specific answer for this.

Once you remove the IA_, use CHARINDEX to find the next unserscore, subtract 1 from that position and that will give you the length of the substring you must use.

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

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 2

Once you remove the IA_, use CHARINDEX to find the next unserscore, subtract 1 from that position and that will give you the length of the substring you must use.

Thanks, I think I got it with this:

declare @string1 nvarchar(30)

declare @string2 nvarchar(30)

declare @string3 nvarchar(30)

SET @string1 = 'IA_JAMESF_1-6YHSAT'

SET @string2 = (SELECT REPLACE(@string1,'IA_',''))

SET @string3 = (select REPLACE(@string2,SUBSTRING(@string2,charindex('_',@string2,-1),20),''))

select @string3

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Jody Claggett-376930 (2/18/2011)
Once you remove the IA_, use CHARINDEX to find the next unserscore, subtract 1 from that position and that will give you the length of the substring you must use.
Thanks, I think I got it with this:
declare @string1 nvarchar(30)
declare @string2 nvarchar(30)
declare @string3 nvarchar(30)
SET @string1 = 'IA_JAMESF_1-6YHSAT'
SET @string2 = (SELECT REPLACE(@string1,'IA_',''))
SET @string3 = (select REPLACE(@string2,SUBSTRING(@string2,charindex('_',@string2,-1),20),''))
select @string3

You might want to take another look at dropping the 'IA_' What happens when your initial is something like 'IA_JULIA_1-7KJDJF'?

You just removed part of the data you want to keep and the second set statement will fail.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

something like this should get you there

declare @name varchar(50) = 'IA_JAMESF_1-6YHSAT'

select Reverse(SUBSTRING(REVERSE(SUBSTRING(@name, CHARINDEX('_', @name) + 1, DATALENGTH(@name))), CHARINDEX('_', REVERSE(SUBSTRING(@name, CHARINDEX('_', @name) + 1, DATALENGTH(@name)))) + 1, DATALENGTH(REVERSE(SUBSTRING(@name, CHARINDEX('_', @name) + 1, DATALENGTH(@name))))))

Kind of a nasty string manipulation but it gets you there. There may be a simpler way to get there that somebody may come up with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

Sean - you beat me and I think yours looks cleaner but I'm posting anyway. 🙂

DECLARE @string VARCHAR(50)

SET @string = 'IA_JOOWEN_1-F3D9NN'

SELECT SUBSTRING(@string, CHARINDEX('_', @string, 1) + 1, ( LEN(@string) - CHARINDEX('_', REVERSE(@string), 1) - CHARINDEX('_', @string, 1) + 1 ) - 1)

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

David - yours is about half as much typing as mine but has a lot more +1 -1 than mine. They both certainly work equally well. :hehe:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

Sean Lange (2/18/2011)
David - yours is about half as much typing as mine but has a lot more +1 -1 than mine. They both certainly work equally well. :hehe:

Yeah - that's why I like yours better. I don't like the +1 -1 junk. I like the use of DATALENGTH that you had. Gleaning. 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 8

Thank you all for helping me out! This was a huge timesaver. I ended up using Sean's code, but I tested David's and that would have worked as well.

SQLServerCentral.com has some of the best community forums. Thank you so much!

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Jody Claggett-376930 (2/18/2011)
Once you remove the IA_, use CHARINDEX to find the next unserscore, subtract 1 from that position and that will give you the length of the substring you must use.
Thanks, I think I got it with this:
declare @string1 nvarchar(30)
declare @string2 nvarchar(30)
declare @string3 nvarchar(30)
SET @string1 = 'IA_JAMESF_1-6YHSAT'
SET @string2 = (SELECT REPLACE(@string1,'IA_',''))
SET @string3 = (select REPLACE(@string2,SUBSTRING(@string2,charindex('_',@string2,-1),20),''))
select @string3

I know it seems terribly obvious but have to make sure... Does that mean the 'IA_' is on [font="Arial Black"]every [/font]row?

If it does, the code can be greatly simplified...

SELECT SUBSTRING(@string,4,CHARINDEX('_',@string,4)-4)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

You hit my question Jeff: what if the rule is "delete characters 1-n where n is the position of the first underscore"? i.e.

AB_mmmmm_somestuff = mmmmm

EEE_yyyyy_somestuff = yyyyy

_ppppp_somestuff = ppppp

This sheds light on the all-too-common issue of insufficient problem definition. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service