All Forums Database
JULIE.H 1 post Joined 07/11
13 Jul 2011
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,

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


Jimm 298 posts Joined 09/07
14 Jul 2011

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.

You must sign in to leave a comment.