How can I select values where first 2 characters are non numeric and last character is 1

Question

Post reply

How can I select values where first 2 characters are non numeric and last character is 1

Steve Kinnaman

SSC Eights!

Points: 839
More actions
November 5, 2013 at 5:34 am

#300742

I need to make a condition where I exclued results from a column where the first 2 characters are not numeric [A...Ö] And the last character is 1. There are only 8 characters for these posts. Other values 4- 5- 10 characters are allowed
Example of results where I want to (only) exclude AL190001 and AJ100001:
contact_idFtgNr
4404 4404
5211 AL190000
5211 AL190001
5624 5624
5982 5982
7279 7279
7420 AJ100000
7420 AJ100001
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE #testEnvironment ([contact_id] [int] NOT NULL,[FtgNr] [nvarchar](49) NULL,)
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(4404,'4404')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(5211,'AL190000')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(5211,'AL190001')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(5624,'5624')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(5982,'5982')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(7279,'7279')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(7420,'AJ100000')
INSERT INTO #testEnvironment ([contact_id],[FtgNr]) VALUES(7420,'AJ100001')

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SELECT

contact_id

,FtgNr

FROM

#testEnvironment

WHERE

FtgNr NOT LIKE '[A-Z][A-Z]%1'

John

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 2

Thank you that was perfect. I was looking for something really difficult, your solution was simpler and elegant

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

Safer might be:

WHERE

FtgNr NOT LIKE '[^0-9][^0-9]%1'

Unless you really want "alphabetic" rather than "non-numeric" as originally stated. Spaces, underscores, periods, etc., would fail this test but pass the other one ;-).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 4

In my specific problem was that the value I'm looking for has 8 characters where the first 2 are alfabetical letters and the 8th character is a 1. http://www.sqlservercentral.com/Forums/Topic1511444-392-1.aspxI acutally had to change it to:

FtgNr NOT LIKE '[A-Ö][A-Ö]%1'

to accommodate Swedish characters. I'll bear in mind you comment for future use.

Thanks for your responce