CODEWITHSUNDEEP
sqldatabase
Younes Nj
Younes Nj

Reputation: 596

SQL: remove hashtag from end of the strings

I have a database like this:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| PosScore    | float       | YES  |     | NULL    |                |
| NegScore    | float       | YES  |     | NULL    |                |
| SynsetTerms | varchar(45) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

some of the SynsetTerms have # at the end. Can I just use an SQL query and remove them?

Thanks in advance.

Upvotes: 0

Views: 991

Answers (2)

JanR
JanR

Reputation: 6132

In your select or update statement just do this:

SELECT replace(synsetTerms, '#','') from table

or

UPDATE table set synsetTerms =  replace(synsetTerms, '#','')

if you just want to update the records that contain the '#' symbol you can add the following WHERE clause:

WHERE synsetTerms like '%#'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You can use an update statement:

update t
   set SynsetTerms = left(SynsetTerms, length(SynsetTerms) - 1)
   where SynsetTerms like '%#';

If you want to remove all occurrences of '#':

update t
   set SynsetTerms = replace(SynsetTerms, '#', '')
   where SynsetTerms like '%#%';

Upvotes: 3

Related Questions