CODEWITHSUNDEEP
sqldatabaseoracle-database
Norman017
Norman017

Reputation: 3

How could I remove unnecessary characters in SQL

I need a query that could remove unnecessary characters (a not-so-needed trailing comma as an example) from the string stored in my database table.

So that

EMAIL_ADD
[email protected],
[email protected],[email protected],
[email protected],

would update it into something like this:

EMAIL_ADD
[email protected]
[email protected],[email protected]
[email protected]

Upvotes: 0

Views: 284

Answers (3)

Jens
Jens

Reputation: 69440

if you only whant to remove the last character of a string you can use 

update mytable set my_column = substr(my_column ,0,len(trim(my_column)-1) where mycolumn like '%,'

It is an untested example.

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156928

If you have a specific list of characters to filter out at the start and end use trim functions:

select ltrim(ltrim(rtrim(rtrim(email_add, ','), ' '), ','), ' ')
from   tableX

Here I nested ltrim and rtrim to remove leading and trailing , and .

Or using trim:

select trim(trim(both ',' from email_add))
from   tableX

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Using TRIM() function with TRAILING option removes a specific unwanted character from end of string , in your case being a comma present at end.

UPDATE tableName
SET EMAIL_ADD = TRIM(TRAILING ',' FROM EMAIL_ADD)

See documentation here TRIM()

Upvotes: 1

Related Questions