right trim

Question

right trim

ronan.healy

SSCrazy

Points: 2232
More actions
August 27, 2014 at 3:34 am

#290161

hi
I want to right trim from a certain point
can I use a rtrim with a substring
SUBSTRING (TaxLotDescription,1,3)
or whats the best way to do it

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

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

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

ronan.healy (8/27/2014)
hi
I want to right trim from a certain point
can I use a rtrim with a substring
SUBSTRING (TaxLotDescription,1,3)
or whats the best way to do it

Left(Col1,[Number of chars to keep])

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronan.healy SSCrazy Points: 2232 More actions · Answer 2

chars might not be the same length. should have stated that sorry but they will be going right

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

So you want to trim from a certain point, and that point varies from one string to the next? Tough.

Can you describe the requirement more clearly please? Some examples would help.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronan.healy SSCrazy Points: 2232 More actions · Answer 4

this is a sample from a column BNY Mellon - 07/01/2014

so all the columns will always have that date format and I want to text the text after the date

Phil Parkin SSC Guru Points: 246956 More actions · Answer 5

ronan.healy (8/27/2014)
this is a sample from a column BNY Mellon - 07/01/2014
so all the columns will always have that date format and I want to text the text after the date

No idea what you mean by 'text the text'.

Nor can I see anything 'after the date'.

If you just want the right 10 characters, use the RIGHT function.

If there are unwanted trailing spaces in your source, use RTRIM() too

Right(RTrim(Col),10)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronan.healy SSCrazy Points: 2232 More actions · Answer 6

sorry I ment the text before the date.

that's the text I want to take

so its like this now BNY Mellon - 07/01/2014

and I want it like this BNY Mellon

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

LEFT or SUBSTRING with a starting position of 1 are fine.

Investigate using the CHARINDEX or PATINDEX functions to find the point at which you want to cut off the string, e.g.

LEFT(ColumnName, PAT/CHARINDEX(Params))

SUBSTRING(ColumnName, 1, PAT/CHARINDEX(Params))

Without knowing the rules you're using to determine where to trim the string it's difficult to be more specific.

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

ronan.healy (8/27/2014)
sorry I ment the text before the date.
that's the text I want to take
so its like this now BNY Mellon - 07/01/2014
and I want it like this BNY Mellon

DECLARE @MyString VARCHAR(200) = 'BNY Mellon - 07/01/2014'

SELECT LEFT(@MyString, LEN(@MyString)-LEN(' - 07/01/2014'))

SELECT LEFT(@MyString, LEN(@MyString)-13)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden