only get data with special pattern

• ahmet.keskin

  SSC Veteran

  Points: 230

  More actions

  August 16, 2012 at 5:13 am

  #262948

  Hi all,

  In my table I have a column where the values starts with special charecters and goes on with numbers and letters.

  The first four letters(ABCD) are prefix and the rest is automatically generated code. As an example:

  ABCD000000001

  ABCD000000002

  ABCD000000003

  ABCDT000000001

  ABCD00000000T1

  ABCD00000T0001

  here the data which contains 'T' are entered by user. The problem is that I want to get only ABCD[anyNumber] patterned data from database. Not with the ones contains any letter.

  I tried

  Select * from Codes

  where Code like 'ABCD[^a-zA-Z]%'

  order by Code desc

  but it only exclude ABCDT0000001 and retrieves all other values that contains letters. How to fix this?

  Any help is appreciated.

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  August 16, 2012 at 5:21 am

  #1526489

  So the first 4 will always be ABCD, then anything after that should be numeric?

  DECLARE @Codes TABLE (Code VARCHAR(100))

  INSERT INTO @Codes VALUES

  ('ABCD000000001'),

  ('ABCD000000002'),

  ('ABCD000000003'),

  ('ABCDT000000001'),

  ('ABCD00000000T1'),

  ('ABCD00000T0001')

  SELECT

  Code

  FROM

  @Codes

  WHERE

  ISNUMERIC(RIGHT(Code,(LEN(Code)-4))) = 1

  ORDER BY

  Code DESC

• August 16, 2012 at 6:21 am

  This was removed by the editor as SPAM

• August 16, 2012 at 6:23 am

  This was removed by the editor as SPAM

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 16, 2012 at 8:46 am

  #1526640

  Be very, very careful about using ISNUMERIC .. for example, altering your input data slightly to more readily see the possible problem.

  DECLARE @Codes TABLE (Code VARCHAR(100),LineNr INT)

  INSERT INTO @Codes VALUES

  ('ABCD000000001',1),

  ('ABCD000000002',2),

  ('ABCD000000003',3),

  ('ABCD$000000001',4), -- notice the $ sign

  ('ABCD00000000T1',5),

  ('ABCD00000T0001',6)

  Executing the suggested:

  SELECT

  Code

  FROM

  @Codes

  WHERE

  ISNUMERIC(RIGHT(Code,(LEN(Code)-4))) = 1

  ORDER BY

  Code DESC

  Results:

  Code

  ABCD000000003

  ABCD000000002

  ABCD000000001

  ABCD$000000001 -- the gotcha

  For further information read this SQL Spackle entry Jeff Moden

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

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  August 16, 2012 at 8:48 am

  #1526641

  bitbucket-25253 (8/16/2012)

  ---

  Be very, very careful about using ISNUMERIC .. for example, altering your input data slightly to more readily see the possible problem.

  DECLARE @Codes TABLE (Code VARCHAR(100),LineNr INT)

  INSERT INTO @Codes VALUES

  ('ABCD000000001',1),

  ('ABCD000000002',2),

  ('ABCD000000003',3),

  ('ABCD$000000001',4), -- notice the $ sign

  ('ABCD00000000T1',5),

  ('ABCD00000T0001',6)

  Executing the suggested:

  SELECT

  Code

  FROM

  @Codes

  WHERE

  ISNUMERIC(RIGHT(Code,(LEN(Code)-4))) = 1

  ORDER BY

  Code DESC

  Results:

  Code

  ABCD000000003

  ABCD000000002

  ABCD000000001

  ABCD$000000001 -- the gotcha

  For further information read this SQL Spackle entry Jeff Moden

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

  POW....right in the kisser

  Good spot and thanks for that, another thing to add to my gotcha lists.

• ahmet.keskin

  SSC Veteran

  Points: 230

  More actions

  August 16, 2012 at 9:05 am

  #1526651

  Stewart "Arturius" Campbell (8/16/2012)

  ---

  Alternatively, consider:

  Select * from Codes

  where Code like 'ABCD%[^a-zA-Z]%'

  order by Code desc

  Thanks, but does not work.

• August 16, 2012 at 9:34 am

  This was removed by the editor as SPAM

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 16, 2012 at 9:36 am

  #1526677

  Try this:

  DECLARE @Codes TABLE (Code VARCHAR(100))

  INSERT INTO @Codes VALUES

  ('ABCD000000001'),

  ('ABCD000000002'),

  ('ABCD000000003'),

  ('ABCDT000000001'),

  ('ABCD00000000T1'),

  ('ABCD00000T0001')

  SELECT

  Code

  FROM

  @Codes

  WHERE

  RIGHT(Code,(LEN(Code)-4)) NOT LIKE '%[^0-9]%'

  ORDER BY

  Code DESC;

  And for more information, read this article: http://www.sqlservercentral.com/articles/IsNumeric/71512/.

  Edit: This also works:

  DECLARE @Codes TABLE (Code VARCHAR(100))

  INSERT INTO @Codes VALUES

  ('ABCD000000001'),

  ('ABCD000000002'),

  ('ABCD000000003'),

  ('ABCDT000000001'),

  ('ABCD00000000T1'),

  ('ABCD00000T0001')

  SELECT

  Code

  FROM

  @Codes

  WHERE

  Code NOT LIKE 'ABCD%[^0-9]%'

  ORDER BY

  Code DESC;

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 16, 2012 at 9:44 am

  #1526683

  Would this work?

  However, my guess is that it would perform really bad.

  SELECT *

  FROM @Codes

  WHERE code LIKE 'ABCD%'

  AND PATINDEX( '%[^0-9]%', REPLACE( code, 'ABCD', '' )) = 0

  EDIT: Never mind, stay with Lynn's solution

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2