Query to find all special symbols in a column

Question

Query to find all special symbols in a column

Selvam Rajendran

SSC-Addicted

Points: 485
More actions
September 2, 2010 at 12:15 am

#228641

Hi all,
In sql 2000 we could see some special characters showing up for a nvarchar field where as in 2005 we couldn't find.
Can anyone help to solve this problem?
Thanks
Selvam R

Viewing 5 posts - 1 through 4 (of 4 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Can you explain what are you trying to find, how are you trying to find it and what is the results of your attempts? Based on the information that you wrote, the only thing that I can write is that you should use a select statement that uses wild cards with the symbols that you are trying to find.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Selvam Rajendran SSC-Addicted Points: 485 More actions · Answer 2

Thanks Adi,

My problem is simple. I have a nvarchar column in a table. Some of the rows have hidden special symbol which I could see only in text result. This symbol shows up as ? in the HTML page. How could I trace out these symbols by query.

The special symbol looks something like this ?. Like a "right turn" symbol.

Thanks

Selvam R ?

Selvam Rajendran SSC-Addicted Points: 485 More actions · Answer 3

Thanks Adi,

My problem is simple. I have a nvarchar column in a table. Some of the rows have hidden special symbol which I could see only in text result. This symbol shows up as ? in the HTML page. How could I trace out these symbols by query.

The special symbol looks something like this ?. Like a "right turn" symbol.

Thanks

Selvam R ?

WayneS SSC Guru Points: 95460 More actions · Answer 4

So, are the only valid characters to be the characters on the keyboard? These will have an ASCII code between 32 and 126. So:

declare @test-2 table (Col1 nvarchar(500));

declare @testdata nvarchar(500);

set @testdata = '';

-- make a piece of sample data with all characters from ascii 1-128

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

SELECT TOP (128) @testdata = @testdata + char(N)

FROM TALLY

ORDER BY N

insert into @test-2 values (@testdata)

-- show the sample data to show some of the junk characters

select * from @test-2

-- you can make this part into a function

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

SELECT N, [Character] = SUBSTRING(Col1, N, 1)

FROM TALLY, @test-2

WHERE N < len(Col1)

AND ascii(SUBSTRING(Col1, N, 1)) NOT BETWEEN 32 and 126

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2