Finding rows with wildcards in the string and rows with spaces in front or at the end of the string.

Question

Post reply

Finding rows with wildcards in the string and rows with spaces in front or at the end of the string.

cadm777

Old Hand

Points: 387
More actions
May 8, 2012 at 3:37 pm

#392212

All,
The vendor I am working with did not add restrictions on a column or their software to prevent the users from entering wildcard characters or spaces in front of or at the end of the name of the item. My best guess is as the user scans packages into the system, sometimes the bar-code reader reads it wrong, and puts wild cards in the name. Also, the spaces in front of or at the end of the name is the result of the user putting the package down on the keyboard while receiving packages. Trying to be the good DBA that I want to be, I want to clean this up so these unprocessed packages can be removed. I have informed the vendor that field constraints are needed. They are working to get that to me. They have been great so far.
I need to find any row that has the following wildcards in the SEAL name:
` ~ ! @ # $ % ^ & * ( ) _ - + = [ { ] } \ | , < . > / ? ; : ' "
I need to find any row that has spaces in front of or at the end of the SEAL name. Spaces in name are OK.
For example:
R R 80 07
RA185204680US
0120931
Here is the query I have come up:
Select SEAL, *
from toolsDB.CONTENT
where SEAL in
(Select SEAL
from toolsDB.CONTENT
where SEAL like
--For Wildcards in front package name
'%''`%' ESCAPE '''' or SEAL like '%''~%' ESCAPE '''' or SEAL like '%''!%' ESCAPE ''''
or SEAL like '%''@%' ESCAPE '''' or SEAL like '%''#%' ESCAPE '''' or SEAL like '%''$%' ESCAPE ''''
or SEAL like '%''%%' ESCAPE '''' or SEAL like '%''^%' ESCAPE '''' or SEAL like '%''&%' ESCAPE ''''
or SEAL like '%''*%' ESCAPE '''' or SEAL like '%''(%' ESCAPE '''' or SEAL like '%'')%' ESCAPE ''''
or SEAL like '%''-%' ESCAPE '''' or SEAL like '%''_%' ESCAPE '''' or SEAL like '%''=%' ESCAPE ''''
or SEAL like '%''+%' ESCAPE '''' or SEAL like '%''[%' ESCAPE '''' or SEAL like '%''{%' ESCAPE ''''
or SEAL like '%'']%' ESCAPE '''' or SEAL like '%''}%' ESCAPE '''' or SEAL like '%''\%' ESCAPE ''''
or SEAL like '%''|%' ESCAPE '''' or SEAL like '%'';%' ESCAPE '''' or SEAL like '%'':%' ESCAPE ''''
or SEAL like '%''"%' ESCAPE '''' or SEAL like '%'',%' ESCAPE '''' or SEAL like '%''<%' ESCAPE ''''
or SEAL like '%''.%' ESCAPE '''' or SEAL like '%''>%' ESCAPE '''' or SEAL like '%''/%' ESCAPE ''''
or SEAL like '%''?%' ESCAPE ''''
--For Wildcards in the rear of the package name
or SEAL like '%''%`' ESCAPE '''' or SEAL like '%''%~' ESCAPE '''' or SEAL like '%''%!' ESCAPE ''''
or SEAL like '%''%@' ESCAPE '''' or SEAL like '%''%#' ESCAPE '''' or SEAL like '%''%$' ESCAPE ''''
or SEAL like '%''%%' ESCAPE '''' or SEAL like '%''%^' ESCAPE '''' or SEAL like '%''%&' ESCAPE ''''
or SEAL like '%''%*' ESCAPE '''' or SEAL like '%''%(' ESCAPE '''' or SEAL like '%''%)' ESCAPE ''''
or SEAL like '%''%-' ESCAPE '''' or SEAL like '%''%_' ESCAPE '''' or SEAL like '%''%=' ESCAPE ''''
or SEAL like '%''%+' ESCAPE '''' or SEAL like '%''%[' ESCAPE '''' or SEAL like '%''%{' ESCAPE ''''
or SEAL like '%''%]' ESCAPE '''' or SEAL like '%''%}' ESCAPE '''' or SEAL like '%''%\' ESCAPE ''''
or SEAL like '%''%|' ESCAPE '''' or SEAL like '%''%;' ESCAPE '''' or SEAL like '%''%:' ESCAPE ''''
or SEAL like '%''%"' ESCAPE '''' or SEAL like '%''%,' ESCAPE '''' or SEAL like '%''%<' ESCAPE ''''
or SEAL like '%''%.' ESCAPE '''' or SEAL like '%''%>' ESCAPE '''' or SEAL like '%''%/' ESCAPE ''''
or SEAL like '%''%?' ESCAPE '''')
or
SEAL in
(Select SEAL
from toolsDB.CONTENT
where PATINDEX(' %', SEAL) > 0)
Order By toolsDB.CONTENT.SEAL
This query does work. However, I am unable to escape the single quote characters. ( ' ) Also, I know there has to be a simpler way to write this query. Thanks for your help.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Do you really mean that all you want to see is things that have other than 0-9 or A-Z or embedded spaces in them?

If so, you can use something like ...

WHERE Seal LIKE '%[^ 0-9A-Z]%'

OR DATALENGTH(LTRIM(RTRIM(Seal)))<>DATALENGTH(Seal)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinu512 SSCoach Points: 15729 More actions · Answer 2

Jeff Moden (5/8/2012)
Do you really mean that all you want to see is things that have other than 0-9 or A-Z or embedded spaces in them?
If so, you can use something like ...
WHERE Seal LIKE '%[^ 0-9A-Z]%'
OR DATALENGTH(LTRIM(RTRIM(Seal)))<>DATALENGTH(Seal)

It looks useful. But, how do you use it?....I tried it in the following query:

--Create Table

Create Table Ex

(Name varchar(max) )

--Insert Sample Data Into Table

Insert Into Ex

Select 'jsdkf__8764+_ sgf'

Union ALL

Select 'ihcd...09()+_ dhg45;;/53'

--Query

Select Name From Ex Where Name LIKE '%[^ 0-9A-Z]%'

OR DATALENGTH(LTRIM(RTRIM(Name)))<>DATALENGTH(Name)

Doesn't seem to be working here.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

cadm777 Old Hand Points: 387 More actions · Answer 3

Jeff,

That worked great! Thanks.

Here is my final query:

Select SEAL, * FROM toolsDB.CONTENT

Where Seal LIKE '%[^ 0-9A-Z]%'

OR DATALENGTH(LTRIM(RTRIM(Seal)))<>DATALENGTH(Seal)

Looks like I need to read up on the LTRIM and RTRIM usage. Thanks again.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

vinu512 (5/9/2012)
Jeff Moden (5/8/2012)
Do you really mean that all you want to see is things that have other than 0-9 or A-Z or embedded spaces in them?
If so, you can use something like ...
WHERE Seal LIKE '%[^ 0-9A-Z]%'
OR DATALENGTH(LTRIM(RTRIM(Seal)))<>DATALENGTH(Seal)
It looks useful. But, how do you use it?....I tried it in the following query:
--Create Table
Create Table Ex
(Name varchar(max) )
--Insert Sample Data Into Table
Insert Into Ex
Select 'jsdkf__8764+_ sgf'
Union ALL
Select 'ihcd...09()+_ dhg45;;/53'
--Query
Select Name From Ex Where Name LIKE '%[^ 0-9A-Z]%'
OR DATALENGTH(LTRIM(RTRIM(Name)))<>DATALENGTH(Name)
Doesn't seem to be working here.

I guess I'd need to know what you mean by "doesn't seem to be working here". If it returned both rows, then it's working just fine because it will find any row that has some other than letters [A-Z], numeric digits [0-9], or embedded spaces. The OP posted that he wanted to ...

I need to find any row that has the following wildcards in the SEAL name:
` ~ ! @ # $ % ^ & * ( ) _ - + = [ { ] } \ | , < . > / ? ; : ' "

Basically, those are all the visual characters on the keyboard that aren't letters, digits, or numeric spaces. My code also finds invisible characters like tabs, hard spaces, etc, not to mention other currency symbols and many accented characters depending on the collation you're using.

To wit, the following example will only return the first two rows because the third row doesn't qualify. It has only letters, numeric digits, and embedded spaces.

--Create Table

drop table Ex

Create Table Ex

(Name varchar(max) )

--Insert Sample Data Into Table

Insert Into Ex

Select 'jsdkf__8764+_ sgf'

Union ALL

Select 'ihcd...09()+_ dhg45;;/53'

UNION ALL

SELECT 'suiyf g nniof7082 8 5795375'

--Query

Select Name From Ex Where Name LIKE '%[^ 0-9A-Z]%'

OR DATALENGTH(LTRIM(RTRIM(Name)))<>DATALENGTH(Name)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

cadm777 (5/9/2012)
Jeff,
That worked great! Thanks.
Here is my final query:
Select SEAL, * FROM toolsDB.CONTENT
Where Seal LIKE '%[^ 0-9A-Z]%'
OR DATALENGTH(LTRIM(RTRIM(Seal)))<>DATALENGTH(Seal)
Looks like I need to read up on the LTRIM and RTRIM usage. Thanks again.

Thanks for the feedback. Just to be sure, do you understand how the "^" character works in LIKE pattrern?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinu512 SSCoach Points: 15729 More actions · Answer 6

OK Jeff.

I was expecting it to work the other way round. It can be really useful. 😛

Thanks 😎

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

You're welcome. Take a look at one of the many uses for it in the following article...

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cadm777 Old Hand Points: 387 More actions · Answer 8

Jeff,

I was looking that up. Then I saw your post. I read the article you suggested on "ISNUMERIC" It was very helpful. I read at the end of the article that the "^" means "NOT". So, in the query you were telling SQL to find everything that's not 0-9 and A-Z.

Question:

In your query you have in the like statement A-Z ('%[^ 0-9A-Z]%'), does this mean to ignore upper case letters only? I modified the query to include lower case letters ('%[^ 0-9A-Za-z]%'). I got the same results, but that's because none of the SEAL data strings have lower case letters. Just curious if adding "a-z" will also ignore lowercase letters?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

cadm777 (5/10/2012)
Jeff,
I was looking that up. Then I saw your post. I read the article you suggested on "ISNUMERIC" It was very helpful. I read at the end of the article that the "^" means "NOT". So, in the query you were telling SQL to find everything that's not 0-9 and A-Z.
Question:
In your query you have in the like statement A-Z ('%[^ 0-9A-Z]%'), does this mean to ignore upper case letters only? I modified the query to include lower case letters ('%[^ 0-9A-Za-z]%'). I got the same results, but that's because none of the SEAL data strings have lower case letters. Just curious if adding "a-z" will also ignore lowercase letters?

Only if your either your server has a case sensitive collation as a default (and I wouldn't wish that on anyone, not even an Oracle user) or if you use a case sensitive COLLATE statement in the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinu512 SSCoach Points: 15729 More actions · Answer 10

Jeff Moden (5/10/2012)
You're welcome. Take a look at one of the many uses for it in the following article...
http://www.sqlservercentral.com/articles/IsNumeric/71512/

Thanx Jeff. The link was very useful.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉