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?

You're trying to update ? to ''? '' is an empty string, which is identical to null in Oracle. — Ben
– Ben, Commented Mar 8, 2012 at 17:25
Possible duplicate of How to replace specific values in a oracle database column? — Vadzim
– Vadzim, Commented Nov 16, 2016 at 17:23

JesseBuesking · Accepted Answer · 2012-03-08 17:13:47Z

18

UPDATE databaseName.tableName
SET columnName = replace(columnName, '?', '''')
WHERE columnName LIKE '%?%'

answered Mar 8, 2012 at 17:13

JesseBuesking

6,6165 gold badges46 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Cave · Accepted Answer · 2012-03-08 21:09:11Z

17

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 <<predicate that returns just the sample data you posted>>

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)) > 0
    OR INSTR(column2,chr(25)) > 0 
    ...
    OR INSTR(columnN,chr(25)) > 0

edited Mar 8, 2012 at 21:09

answered Mar 8, 2012 at 17:24

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

11 Comments

WowBow Over a year ago

That might be true. I'll look in to it. Though I have no idea what NLS_LANG is. The data is stored as VARCHAR2 Thanks

WowBow Over a year ago

@Justin .. I found out that in the database. The character is not even visible... it is empty space. By running the dump method on the column name i found this .>> Typ=1 Len=38 CharacterSet=US7ASCII: 53,74,75,64,65,6e,74,19,73,20,53,53,4e,20,6d,61,74,63,68,2c,20,62,75,74,20,6e,6f,20,6e,61,6d,65,20,6d,61,74,63,68. What does it mean? And when it is displayed it is not even question mark but some wired character. The character contains something that looks like 0019.

Justin Cave Over a year ago

@WowBow - Is that the DUMP that corresponds to the sample data you posted ("Parents? CUI assumed equal to the sum of the father?s/stepfather?s and mother?s/ stepmother?s income .")? Those two don't seem to match up.

WowBow Over a year ago

Oh I am sorry. The data is "Students SSN match, but no name match". But as you can see i copy pasted from the database and that character is gone. I have no idea why ... it was supposed to be ,>> Student?s but the question mark is gone when I paste it here

Justin Cave Over a year ago

@WowBow - OK. That makes more sense. Are the problem characters always 0x19?

|

Curtis · Accepted Answer · 2012-03-08 17:26:39Z

10

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 '%?%'

edited Mar 8, 2012 at 17:26

answered Mar 8, 2012 at 17:11

Curtis

104k69 gold badges280 silver badges359 bronze badges

4 Comments

WowBow Over a year ago

don't we need a where clause?

Curtis Over a year ago

@MatX - A where clause will just filter the rows so it only checks the ones which need updating. The only advantage is performance, but its a worthwhile point. I'll add it to the query

WowBow Over a year ago

Oh ... how about if I am updating more than one column .. i.e I will have more than one SET statements. How should I write the where clause in that case because I am dealing with more than one column?

Curtis Over a year ago

@MatX - I've updated my answer. It might be worth checking what Justin is saying first though to ensure your data actually contains ?

user3781264 · Accepted Answer · 2014-06-26 23:32:32Z

0

Use the REPLACE function.

eg: SELECT REPLACE ('t?es?t', '?', 'w');

Source

answered Jun 26, 2014 at 23:32

user3781264

171 bronze badge

1 Comment

Jon Heller Over a year ago

Two other answers say the same thing.

Collectives™ on Stack Overflow

How to replace a character from a String in SQL?

4 Answers 4

Comments

11 Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

11 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related