Clever Geek Handbook

SQL Find Substitute character as part of string

I need help writing a query that will find the Replaced Character in a SQL table.

I have several cells that contain this character and I want to find all of these cells. This is what the cell value looks like:

Thanks for your help!

+3


source to share


3 answers


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
+2


source


The UNICODE clause did not work for me - the was character was treated as a question mark, so the query found all lines with question marks, but not the ones 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, '!')
+2


source


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

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

This query will find records that contain a replacement character.

0


source






More articles:


All Articles