Select value up to number

[cwsstins]

I need to select distinct customer names from a table. Some customers have numbers in their name (store number) after the actual name.

I was trying out this statement to get basically the first word (everything up to a space), but this doesn't work for those customers that have more than one word in their name.

select distinct substr(col1,1,instr(col1,' ')) from tbl where col2 = 'T'

For instance, this data:
Joe's Bait Shop
Walgreen's 203
Walgreen's 392
Smith Upholstery 123
Icing

must return:
Joe's Bait Shop
Walgreen's
Smith Upholstery
Icing

How can I indicate "any number" as the place to stop for the instr function?

 

[sem]

select distinct rtrim(col1,'1234567890') from tbl
where col2 = 'T'

Regards, Dima

http://seminihin.narod.ru

 

[cwsstins]

Thanks sem...I tried that and it's still returning values with numbers, e.g.

DOMINICKS 1312
DOMINICKS 1312
DOMINICKS 1312
DOMINICKS 1691

 

[sem]

So add a blank space to a set to be trimmed. You asked to remove numbers, not non-letters.

select distinct rtrim(col1, '1234567890 ') from tbl
where col2 = 'T'

Regards, Dima

http://seminihin.narod.ru