Using like to identify a value that contains alpha-numeric values other thna A-Z

Question

Post reply

Using like to identify a value that contains alpha-numeric values other thna A-Z

simon_s

Ten Centuries

Points: 1358
More actions
May 11, 2015 at 9:39 am

#318087

I may feel the need to leap out my (ground floor) window with this one
Trying to use LIKE / NOT LIKE to identify values that contain any alpahumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc
The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have charactcters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....
Any words of wisdom or is it nigh on impossible ?!
thanks simon

Viewing 8 posts - 1 through 7 (of 7 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I don't 100% understand your question but think that PATINDEX will help if you're asking what I think you're asking...

You want fields that only contain the letters A through G and/or 0-9? If so this would do the trick:

WITH examples AS

(

SELECT *

FROM (VALUES('ABCD1234567'),('1A2B34567CCC'),('XXBCD1234567'),('ABCD12345%%%67'),('ABCD1234567!')) t(ex)

)

SELECT *

FROM examples

WHERE PATINDEX('%[^A-G0-9]%', ex) = 0

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

LIKE would do as well:

WHERE ex NOT LIKE '%[^A-G0-9]%'

-- Gianluca Sartori

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

spaghettidba (5/11/2015)
LIKE would do as well:
WHERE ex NOT LIKE '%[^A-G0-9]%'

... and would be better than what I posted. I have not finished my coffee this morning and need to do that.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

simon_s Ten Centuries Points: 1358 More actions · Answer 4

thank you both for your replies 🙂 to clarify I want is to return rows that only contain the letters/characters outside of A through G and outside of 1234567890

so for example Im not interested in this row

ABG1226807

but these I need to know about !

@BC1224706

AB!C1224706

ABCZ122470

ABC 122470

i.e if the alphanumber is not between A-G or if the row contains a space I wish to see it (so I can weep at the fact it even exists and then work out how to replace it !)

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Just reverse the NOT:

WITH examples AS

(

SELECT *

FROM (

VALUES

('ABG1226807'),

('@BC1224706'),

('AB!C1224706'),

('ABCZ122470'),

('AABC 122470')

) t(ex)

)

SELECT *

FROM examples

WHERE ex LIKE '%[^A-G0-9]%'

-- Gianluca Sartori

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

simon_s (5/12/2015)
thank you both for your replies 🙂 to clarify I want is to return rows that only contain the letters/characters outside of A through G and outside of 1234567890

Then

WHERE ex LIKE '%[^A-G0-9]%'

simon_s Ten Centuries Points: 1358 More actions · Answer 7

spaghettidba (5/12/2015)
Just reverse the NOT:
WITH examples AS
(
SELECT *
FROM (
VALUES
('ABG1226807'),
('@BC1224706'),
('AB!C1224706'),
('ABCZ122470'),
('AABC 122470')
) t(ex)
)
SELECT *
FROM examples
WHERE ex LIKE '%[^A-G0-9]%'

works wonderfully ...

thank you one and all !