Query to find rows which has replacement character as part of a column string

Question

Post reply

Query to find rows which has replacement character as part of a column string

krishnamurali2489

Mr or Mrs. 500

Points: 517
More actions
September 9, 2018 at 4:04 pm

#408408

Hi All,
I have a column in my oracle SQL table where user will city name from the online application.
But sometimes the column gets updated with a replacement character as part of the string. (Question Mark symbol with black background in diamond shape).
I dont have any idea how this symbol gets appended as part of the string. This is causing issues in our application.
I am trying to find how many rows got affected with this data. But I could not able to come up with correct query (I am trying with regex exp).
Select * from BillPayCust
Where City like '%[^a-z0-9]%';
Above is not working, please help me on this.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63430 More actions · Answer 1

Is this a SQL Server question or an Oracle question?

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 2

Hi ,

My Database is in Oracle but I think the solution to my problem will work in both SQL and Oracle.Please let me know if otherwise.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 3

This is a Unicode character making it into your data. You need to determine what the code is and do a replace.
Google is your friend.

sathwik.em91 SSC Eights! Points: 878 More actions · Answer 4

krishnamurali2489 - Sunday, September 9, 2018 4:04 PM
Hi All,
I have a column in my oracle SQL table where user will city name from the online application.
But sometimes the column gets updated with a replacement character as part of the string. (Question Mark symbol with black background in diamond shape).
I dont have any idea how this symbol gets appended as part of the string. This is causing issues in our application.
I am trying to find how many rows got affected with this data. But I could not able to come up with correct query (I am trying with regex exp).
Select * from BillPayCust
Where City like '%[^a-z0-9]%';
Above is not working, please help me on this.

If you are working on sql server
Please use this
REPLACE(Yourcolumn, 'Your special charecter', '')
Fist I would say test it and then apply it in you code.
Let me know if this helps.

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 5

Chris Hurlbut - Monday, September 10, 2018 2:16 PM
This is a Unicode character making it into your data. You need to determine what the code is and do a replace.
Google is your friend.

Hi Chirs,

The data is coming from the online application (when user enters the data in a specific page). We have found the root-cause of the problem now and provided fix for this so that it will no longer happen again.

Now I need to find out how many rows are already affected in the table to analyze the impact . I have googled for a while and tried few queries to fetch the records but it did not work.

Can you please help me in using the correct SQL query to retrieve the records which has the replacement character (Question Mark with Diamond Background)

Hi SathWik,

I dont want to replace the column value. I want to retrieve the impacted rows first . Thanks !!!

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 6

Declare @Foo Table(PK int primary key identity, MyData nvarchar(20));

Insert @Foo(MyData) Values (N'abc'), (N'ab?c'), (N'abc?')

Select * From @Foo Where MyData Like N'%?%'

-- Find rows with the character

Select * From @Foo

Where CharIndex(nchar(65533) COLLATE Latin1_General_BIN2, MyData) > 0

-- Update rows replacing character with a !

Update @Foo

set MyData = Replace(MyData, nchar(65533) COLLATE Latin1_General_BIN2, '!')

Select * From @Foo

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 7

This is the way to do it in MS SQL, I don't do Oracle (Sorry)