Lostaunaum Lostaunaum - 5 months ago 20
SQL Question

Replacing ' symbol from a column

My data is currently in this format

'54-0000-1747'

I am trying to run the following query

SELECT REPLACE([CitationNumber],''','') AS [CitationNumber] FROM TexasHarrisCountyJPPublicRecords


This query currently does not work because the single quote symbol is trying to convert the rest of the query into a string. All the entries in my column have a single quote at the start and at the end of the string and they are all string values, if they have no value they have '' in the cell.

Is there a syntax that will allow me to search for all single quotes in my column and replace them them with an empty string?

UPDATE

The query to actually change the data in your table is the following:

UPDATE TexasHarrisCountyJPPublicRecords SET [CitationNumber] = REPLACE([CitationNumber], CHAR(39), '')

Answer

If using SQL Server, use the Ascii code 39 representation to find the single quote. Not as messy in my opinion:

SELECT REPLACE(CitationNumber, CHAR(39), '')
Comments