hi all, have an issue:

basically, I am truncating part of a field called city. Im taking off the last 3 characters in this field and joining to another table.

So e.g., a city in t is "Dallas, TX"

I do

where c.city = SUBSTRING(t.city,1, LEN(t.city)-4)

where c.city is Dallas and t.city is "Dallas, TX"

it all works BUT for some rows the city in t.city is just 3 characters, "All".
In this case it doesnt work cause Im doing SUBSTRING("All", 1, 3-4)

you see what Im saying?

but if I could pad t.city with spaces to bring it to AT LEAST 6 characters, if it was Dallas, TX it wouldnt matter but if it was "All", it would become "All " which I can run the above query on.

Thing is, Ive found several lpad and rpad user functions online but these when done rpad on work on "All" but make "Dallas, TX" into "Dallas".

so that doesnt work either.

any ideas?

thanks a bunch!

Moazzam