How do I delete a comma at the end of a string?

Database

How do I delete a comma at the end of a string?

I am creating a view in Teradata and I am trying to join two different tables to pull information back from one of the collumns. However, the information in one of the tables has a comma at the end of some of the data therefore it is not joining the other table correctly.

There are about 112 different pieces of information in this collumn and some have commas and some do not.

Example Henry,
Jackson
Cass
Benton,
Vernon,

I think I would need to do a TRIM statement but I am not sure how to set that up?

Thanks!
1 REPLY
Enthusiast

Re: How do I delete a comma at the end of a string?

If the name field is a varchar, then:

Select Trim(Trailing ',' From Namestr)
From Table
;

If it is not a varchar (ie it is char), then cast it as a varchar. So:

Select PK,Trim(Trailing ',' From (Namestr (varchar(40)))) As sNam
From T1
Order By 1
;

I don't know how often you run this query (hence why do you need a view?), but I would consider cleaning the column up in your data - ie do an update to remove the comma where necessary. Joining things like names/ addresses where there is no index and no stats can be a heavy process. There is no point in collecting stats or indexing on a column when the join condition contains functions like trim/ substring/ cast.