trimming a string

Question

trimming a string

xenophilia

SSCommitted

Points: 1891
More actions
April 11, 2012 at 3:08 pm

#256246

How would you trim a string that looks like this? I need only the string between the first set of backslashes.
\Beverages\Soda Pop\Mountain Dew
-

Viewing 15 posts - 1 through 15 (of 32 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

xenophilia (4/11/2012)
How would you trim a string that looks like this? I need only the string between the first set of backslashes.
\Beverages\Soda Pop\Mountain Dew

Is the first backslash always in position 1 of the string?

xenophilia SSCommitted Points: 1891 More actions · Answer 2

xenophilia

SSCommitted

Points: 1891

April 11, 2012 at 3:13 pm

#1472253

yes (thanks again Lynn)

-

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

See if this helps you:

DECLARE @tststr VARCHAR(64);

SET @tststr = '\Beverages\Soda Pop\Mountain Dew ';

SELECT SUBSTRING(RIGHT(@tststr,DATALENGTH(@tststr) - 1),1,PATINDEX('%\%',RIGHT(@tststr,DATALENGTH(@tststr) - 1)) - 1);

xenophilia SSCommitted Points: 1891 More actions · Answer 4

can i substitute the column name for the @parameter?

eg.

,SUBSTRING(RIGHT('DTS.SuitePath',DATALENGTH('DTS.SuitePath') - 1),1,PATINDEX('%\%',RIGHT('DTS.SuitePath',DATALENGTH('DTS.SuitePath') - 1)) - 1)

as 'SuitePath'

-

xenophilia SSCommitted Points: 1891 More actions · Answer 5

BTW, the string between the first pair of backslashes will vary in length from row to row

-

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes, but not in quotes.

'DTS.SuitePath' means the literal string value DTS.SuitePath. If it's a column name, it must not be in quotes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Instead of constantly asking questions how about trying things out yourself?

xenophilia SSCommitted Points: 1891 More actions · Answer 8

Hello Gail!

So, that results in empty cells. I know I should post some ddl, but any ideas?

-

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

xenophilia (4/11/2012)
BTW, the string between the first pair of backslashes will vary in length from row to row

And your point? The only fixed point the first \ in the string. I'm not looking for that per your specifications.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Lynn Pettis

SSC Guru

Points: 442467

April 11, 2012 at 3:30 pm

#1472272

Are we talking T-SQL code or SSIS?

CapnHector SSCoach Points: 16143 More actions · Answer 11

xenophilia (4/11/2012)
Hello Gail!
So, that results in empty cells. I know I should post some ddl, but any ideas?

so why dont you post what you know we will ask for? it will get you better answers to your questions.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

xenophilia SSCommitted Points: 1891 More actions · Answer 12

xenophilia

SSCommitted

Points: 1891

April 11, 2012 at 3:35 pm

#1472279

ok, because I'm stressed 🙂 I will.

-

xenophilia SSCommitted Points: 1891 More actions · Answer 13

Here's the query. I am using the column name in place of the parameter. It is a working query before I add the trimming functions (line 2). I know Lynn's select string works great, but when I adopt using column name it fails in my query.

------i get this error

Msg 536, Level 16, State 1, Line 1

Invalid length parameter passed to the substring function.

---query

removed

-

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Table definitions and sample data please (sample data as insert statements)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass