CODEWITHSUNDEEP
sqlt-sql
Hemus San
Hemus San

Reputation: 399

SQL Find Replacement Character as a part of a string

I need help with writing a query which will find Replacement Character in SQL table.

I have multiple cells which contain that character and I want to find all those cells. This is how the value of cell looks like this:

Thank you for your help!

Upvotes: 9

Views: 8782

Answers (5)

mirazimi
mirazimi

Reputation: 878

REPLACE - character to ,

UPDATE TblName
SET fildname= REPLACE(fildname, NCHAR(UNICODE('-')), N'،')

Upvotes: 0

Kailash Mali
Kailash Mali

Reputation: 87

In Sql you can able to replace the black diamond symbol.

Input : You�ll be coached through alternating Output : You will be coached through alternating select replace(description, nchar(65533) COLLATE Latin1_General_BIN2,' wi') from [Fitnesstable] where description LIKE '%' + '. You'+NCHAR(55296) +'ll'+ '%'

Upvotes: 0

Fijjit
Fijjit

Reputation: 1477

The UNICODE suggestion didn't work for me - the � character was being treated as a question mark, so the query was finding all strings with question marks, but not those with �.

The fix posted by Tom Cooper at this link worked for me: https://social.msdn.microsoft.com/forums/sqlserver/en-US/2754165e-7ab7-44b0-abb4-3be487710f31/black-diamond-with-question-mark

-- Find rows with the character
Select * From [MyTable]
Where CharIndex(nchar(65533) COLLATE Latin1_General_BIN2, MyColumn) > 0

-- Update rows replacing character with a !
Update [MyTable]
set MyColumn = Replace(MyColumn, nchar(65533) COLLATE Latin1_General_BIN2, '!')

Upvotes: 11

FriendlyManCub
FriendlyManCub

Reputation: 81

Use the Unicode function:

DECLARE @TEST TABLE (ID INT, WORDS VARCHAR(10))

INSERT INTO @TEST VALUES (1, 'A�AA')
INSERT INTO @TEST VALUES (2, 'BBB')
INSERT INTO @TEST VALUES (3, 'CC�C')
INSERT INTO @TEST VALUES (4, 'DDD')

SELECT * FROM @TEST WHERE WORDS LIKE '%' + NCHAR(UNICODE('�')) + '%'

UPDATE @TEST
SET WORDS = REPLACE(WORDS, NCHAR(UNICODE('�')), 'X')

SELECT * FROM @TEST WHERE WORDS LIKE '%' + NCHAR(UNICODE('�')) + '%'
SELECT * FROM @TEST

Upvotes: 3

jmsds
jmsds

Reputation: 225

Try the following code to search the query for the character you wish to find.

select field_name from tbl_name where instr(field_name, 'charToBeSearched') > 0;

This query will find and selects the records which has replacement character.

Upvotes: -1

Related Questions