Trimming Left and Right of side of cell

Question

Trimming Left and Right of side of cell

RazorbackDBA

SSC Eights!

Points: 867
More actions
June 18, 2014 at 6:18 pm

#294711

Hello all,
I am looking for a script to trim the left side and right of a cell. I can trim either side but not at the same time. Any ideas would help. Trying to trim both sides of column1.
Select right(column1, 37) as new_Column1
from table1

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Are you looking for LTRIM/RTRIM for whitespace trimming, or some form of SUBSTRING() manipulation to grab the center of actual text? We'll need some more details to help you on this.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

RazorbackDBA SSC Eights! Points: 867 More actions · Answer 2

RazorbackDBA

SSC Eights!

Points: 867

June 18, 2014 at 6:55 pm

#1723044

I just posted a reply not sure if it went through.

RazorbackDBA SSC Eights! Points: 867 More actions · Answer 3

Thanks Craig Farrell,

THe column is a (nvarchar(255),null)

The column read as: 'Update record GUID{1231jk-2322yy-2883487-dhe33}'

I can trim the left side giving me the result: '1231jk-2322yy-2883487-dhe33}'

But I need only the GUID: '1231jk-2322yy-2883487-dhe33'

Thanks,

Clarence

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Like this?

DECLARE @testdata nVARCHAR(255)

SET @testdata = N'Update record GUID{1231jk-2322yy-2883487-dhe33}'

SELECT

SUBSTRING( @testdata, CHARINDEX( '{', @testdata) + 1, CHARINDEX( '}', @testdata) - CHARINDEX( '{', @testdata) - 1)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

RazorbackDBA SSC Eights! Points: 867 More actions · Answer 5

Craig,

Will this also work on a column and not just the cell?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Replace @testdata with your column name and you should be good to go.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

RazorbackDBA SSC Eights! Points: 867 More actions · Answer 7

Yes, I did that but it is still only trimming the GUID that I list in the

SET @ACTIVITY = N'updating records for GUID {121212-12121h-121233-dhf3}'

This is the only result I get back that is trimmed. I would like to apply this on the entire column.

Thanks again for all your help.

Ed Wagner SSC Guru Points: 287015 More actions · Answer 8

If you want to know all about the SUBSTRING function, it's covered at http://msdn.microsoft.com/en-us/library/ms187748%28v=sql.100%29.aspx. If you have any questions about it, that's a good page to read.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

RazorbackDBA SSC Eights! Points: 867 More actions · Answer 9

Thanks Ed, that help solved the problem.

Thanks, Clarence