How to replace a character from a String in SQL

Question

I have 100 s of cells in our database which contain   instead of    It is possible that this might happen in all rows and columns and in more than one word per cell  Here is an example of just one cell    Parents  CUI assumed equal to the sum of the father s stepfather s and mother s  stepmother s income     I want to write an SQL statement which finds all the cells that contain    may be more than one per cell   and replace them with    I am sure that all   have to be replaced without exception   I know there is a function replace but I couldn t know how to extract a character from a string in sql    This is one example I got but it couldn t help me   UPDATE dbo authors  SET    city   replace city   Salt    Olympic   WHERE  city LIKE  Salt      Any ideas

User · Answer

UPDATE databaseName tableName SET columnName   replace columnName             WHERE columnName LIKE

User · Answer

Use the REPLACE function   eg  SELECT REPLACE   t es t         w     Source

User · Answer

Are you sure that the data stored in the database is actually a question mark   I would tend to suspect from the sample data that the problem is one of character set conversion where   is being used as the replacement character when the character can t be represented in the client character set   Possibly  the database is actually storing Microsoft  smart quote  characters rather than simple apostrophes   What does the DUMP function show is actually stored in the database   SELECT column name         dump column name 1016    FROM your table  WHERE  lt  lt predicate that returns just the sample data you posted gt  gt    What application are you using to view the data   What is the client s NLS LANG set to   What is the database and national character set   Is the data stored in a VARCHAR2 column   Or NVARCHAR2   SELECT parameter  value   FROM v nls parameters  WHERE parameter LIKE   CHARACTERSET     If all the problem characters are stored in the database as 0x19  decimal 25   your REPLACE would need to be something like  UPDATE table name    SET column1   REPLACE column1  chr 25   q               column2   REPLACE column2  chr 25   q                          columnN   REPLACE columnN  chr 25   q        WHERE INSTR column1 chr 25    gt  0     OR INSTR column2 chr 25    gt  0              OR INSTR columnN chr 25    gt  0

User · Answer

This will replace all   with     UPDATE dbo authors     SET    city   replace city             WHERE city LIKE         If you need to update more than one column  you can either change city each time you execute to a different column name  or list the columns like so   UPDATE dbo authors     SET    city   replace city                    columnA   replace columnA             WHERE city LIKE       OR columnA LIKE

[sql] How to replace a character from a String in SQL?

Examples related to sql

Examples related to oracle

Examples related to replace