SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to find all special symbols in a column


Query to find all special symbols in a column

Author
Message
Selvam Rajendran
Selvam Rajendran
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 98
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
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3655 Visits: 6503
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
Selvam Rajendran
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 98
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
Selvam Rajendran
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 98
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
WayneS
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9823 Visits: 10569
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 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 values (@testdata)

-- show the sample data to show some of the junk characters
select * from @test

-- 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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search