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

Searching removing special characters using LIKE pattern or Patindex Expand / Collapse
Author
Message
Posted Tuesday, September 29, 2009 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:52 AM
Points: 2, Visits: 41
I'm looking for the best way of removing certain special characters below CHAR 32, from string data (nvarchar) in a table column.

I'd like to keep some characters such as TAB (9) and CRLF (13 and 10) in my strings, but my main issue is that I'm not quite sure how and if ranges including certain characters are valid.

Including CHAR(14) for some reason, seems to make everything fail. Here is my test code, and the results:

SELECT COUNT(*) FROM myTable WHERE col1 LIKE '%[' + CHAR(1) + '-' + CHAR(13)+']%'
-- Result: 344697

SELECT COUNT(*) FROM myTable WHERE col1 LIKE '%[' + CHAR(1) + '-' + CHAR(14)+']%'
-- Result: 0

/* THE SAME TESTS WITH PATINDEX */
DECLARE @findstr NVARCHAR(50)
SET @findstr = '%[' + CHAR(1) + '-' + CHAR(13) + ']%'
SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0
-- Result: 344697

DECLARE @findstr NVARCHAR(50)
SET @findstr = '%[' + CHAR(1) + '-' + CHAR(14) + ']%'
SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0
-- Result: 0

Why do I get a 0 result when expanding the CHAR range...?

Best regards,
Andreas
Post #795063
Posted Tuesday, September 29, 2009 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:52 AM
Points: 2, Visits: 41
I now tried to build a long string without ranges, and that seemed to work.
DECLARE @findstr NVARCHAR(50)
SET @findstr = '%['+CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)+CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)+CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)+CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)+CHAR(30)+CHAR(31)+']%'
SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

-- Result: 5

Also, for my current purposes, building up ranges not including char 9, 10 and 13 results in the same 5 records:

DECLARE @findstr NVARCHAR(50)
SET @findstr = '%['+CHAR(1)+'-'+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+'-'+CHAR(31)+']%'
SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

So... I'm still not able to figure out why the SQL in my initial question does not work... probably something with Char(13)... even though it was not until I included CHAR(14) in the range that the result became 0 rows. Does anyone have an explanation for that?
Post #795110
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse