Using LTRIM on a column within a derived table

Question

Post reply

Using LTRIM on a column within a derived table

copett86

SSC-Addicted

Points: 435
More actions
July 14, 2011 at 11:02 am

#244055

Hello all,
My issue is with leading white spaces in my records. The white spaces range from 5 spaces to 16 spaces (yes I know it's wild, not my call).
First I used the LTRIM without a 'WHERE' criteria and it works but pulls back everything. I need to select all records beginning with the number 8.
So in my where clause I use " where column_name LIKE '8%' " but LTRIM does not physically trim the data, so my LIKE fails and returns nothing.
I am unable to physically remove the white spaces in the column, so that is not an option. I was thinking about creating a derived table using LTRIM within the derived table, but that errors out. Please see below.
select distinct
T2.Number
from
table1 as T1
JOIN (select LTRIM(number))
from table1
) T2 on T2.Number = T1.Number
where T2.Number like '8%'
When I run this it errors out saying no column name was specified....etc. When I remove the LTRIM from the derived table, it works fine but returns nothing just like it did without the derived table (As mentioned at the beginning).
Another option is using about 12 different 'LIKE' 'OR' 'LIKE', but that just sounds sloppy and not the best way of handling the situation.
I know there are other ways of handling this, I am interested in the the best practice of how to handle this situation. If there is not a best way, then something very close will be fine 🙂
Thank you all for your time, I greatly appreciate the help!
CP

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the trimmed field needs to be aliased...same as if you did a min or max, for example.

SELECT DISTINCT

T2.Number

FROM table1 AS T1

INNER JOIN (SELECT

LTRIM(number) AS Number

FROM table1

) T2

ON T2.Number = T1.Number

WHERE T2.Number LIKE '8%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bopeavy SSCertifiable Points: 5869 More actions · Answer 2

Your query would work better if you had some sort of id.

select distinct

T2.Number

from

table1 as T1

JOIN

table1 as T2

on T2.ID = T1.ID

where LTRIM(T2.Number) like '8%'

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

copett86 SSC-Addicted Points: 435 More actions · Answer 3

Thanks for the quick response guys.

I guess after mashing my face on the keyboard my vision was too blurry to notice I didnt give an alias 😀

It's working meow!

Thanks again!

CP