Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to find all special symbols in a column Expand / Collapse
Author
Message
Posted Thursday, September 2, 2010 12:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 4, 2012 4:23 AM
Points: 147, 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


Post #979314
Posted Thursday, September 2, 2010 1:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 2,114, Visits: 5,502
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/
Post #979324
Posted Friday, September 3, 2010 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 4, 2012 4:23 AM
Points: 147, 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 ‎
Post #980195
Posted Friday, September 3, 2010 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 4, 2012 4:23 AM
Points: 147, 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 ‎
Post #980196
Posted Friday, September 3, 2010 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
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
Post #980217
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse