Remove all instances of a character from a column within tag

replace

I don't know much about writing SQL and a client of mine has an erroneous semicolon appearing in his database that he'd like removed. It's in 1 specific column, but it should only be removed if it is within an tag. For example:

MyColumn
--------
<div style="this;is;ok;">This is okay; but <img src="this;"/> is not</div>

Can this be done?

Best Answer

Short answer - yes, it could be done

Correct and full answer depends from other information

For single (or predefined number of tags) - You can prepare UPDATE SQL for XML nodes and use REPLACE T-SQL function for updated parameters

The is will work if You need remove all ";" from TAG, if logic more complicated - solution will not work.

Related Solutions

Mysql – Remove the last character from Variable String

SELECT
    SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ;

If you want update the strings from a table, chopping off what is on the right of .com:

UPDATE 
    tableX
SET
    col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) 
WHERE
    SUBSTRING_INDEX(col, '.com', -1) <> col ;

Db2 – Remove single quotes from db2 column name

As you found, you have to use '' to represent a single quote (').

You do not need to use the SPACE() function:

select replace(character_column, '''', '')
  from your_table

Note: Using SPACE() could actually create an issue as it returns VARCHAR(4000).

Best Answer

Related Solutions

Mysql – Remove the last character from Variable String

Db2 – Remove single quotes from db2 column name

Related Question