Trim/Remove Leading Characters 1

[jiggyg]

Hello List!

I have a question on how to trim chars off the front of data...

I added 'zz_' to the front of all my floor #'s in order to get them to import from Excel to SQL; now, in SQL I want to get rid of them -- How can I do that?

For example:
In the database, the column is named 'Floor *'
Floor * Want
zz_01 01
zz_2 2
zz_B B
zz_36-W 36-W


Thanks much for your time and help!
-jiggyg

 

[gmmastros]

something like this...

Update TableName
Set    ColumnName = Right(ColumnName, Len(ColumnName)-3)
Where  ColumnName Like 'zz_%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jiggyg]

Thanks, George!

I also found another way to do it and they both worked!

UPDATE TableName
SET ColumnName = LTRIM(REPLACE(ColumnName, 'zz_', ''))


Thanks much! Great board and even greater people here!

 

[Crowley16]

you don't need the ltrim

however this way, if you had something like:

zz_13zz_

then you would end up with:

13

--------------------
Procrastinate Now!

 

[gmmastros]

jiggyg

There are a couple of potential problems with your query. I suspect that it doesn't really matter here, but this is useful information to have for later.

First. if zz_ appears multiple times in your data, each time it appears it will be replaced with an empty string.

Next. If zz_ appears in your data, but not at the beginning, it will still be replaced.

[COLOR=blue]Declare[/color] @Data [COLOR=blue]VarChar[/color](100)

[COLOR=blue]Set[/color] @data = [COLOR=red]'zz_This is data(zz_).'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]LTRIM[/color]([COLOR=#FF00FF]Replace[/color](@Data, [COLOR=red]'zz_'[/color],[COLOR=red]''[/color])) [COLOR=green]-- This is data().[/color]

I'm not saying that your method is bad, or wrong, I'm just trying to point out the implications of it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

And taking it all back a step, there are ways to get the data to import to SQL Server properly without having to use tricks like sticking characters on the front...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]