Greater/Less than on text column

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  May 29, 2013 at 8:48 am

  #293930

  Hey,

  I have a column (INVOICENO) which is nvarchar(12). It's this datatype as an invoice could be a credit note, so xxCN, or debit note so xxDN.

  All other values are just integers, starting a 1. How would I go about showing only say invoices 10-20, including anything CN, DN etc.

  10

  11DN

  12

  13

  14

  15

  16

  17CN

  18DN

  19

  20

  etc... not my application (3rd party) so cannot change datatype.

  Not hopeful, but thanks!

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 29, 2013 at 9:08 am

  #1619348

  Something like this perhaps:

  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

  DROP TABLE #tmp;

  CREATE TABLE #tmp (val varchar(10) primary key)

  GO

  INSERT INTO #tmp

  SELECT '5'UNION ALL

  SELECT '6DN'UNION ALL

  SELECT '7'UNION ALL

  SELECT '10'UNION ALL

  SELECT '11DN'UNION ALL

  SELECT '12'UNION ALL

  SELECT '13'UNION ALL

  SELECT '14'UNION ALL

  SELECT '15'UNION ALL

  SELECT '15XX'UNION ALL

  SELECT '15YY'UNION ALL

  SELECT '16'UNION ALL

  SELECT '17CN'UNION ALL

  SELECT '18DN'UNION ALL

  SELECT '19'UNION ALL

  SELECT '20';

  SELECT *

  FROM #tmp

  WHERE ISNUMERIC(LEFT(val,2))=1

  AND CAST(LEFT(val,2) AS tinyint) BETWEEN 10 AND 20

  AND (RIGHT(val,2) IN ('CN','DN') OR LEN(val)=2)

  DROP TABLE #tmp

  GO

  "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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 29, 2013 at 9:31 am

  #1619359

  How about this:

  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

  DROP TABLE #tmp;

  CREATE TABLE #tmp (val varchar(10) primary key)

  GO

  INSERT INTO #tmp

  SELECT '5'UNION ALL

  SELECT '6DN'UNION ALL

  SELECT '7'UNION ALL

  SELECT '10'UNION ALL

  SELECT '11DN'UNION ALL

  SELECT '12'UNION ALL

  SELECT '13'UNION ALL

  SELECT '14'UNION ALL

  SELECT '15'UNION ALL

  SELECT '15XX'UNION ALL

  SELECT '15YY'UNION ALL

  SELECT '16'UNION ALL

  SELECT '17CN'UNION ALL

  SELECT '18DN'UNION ALL

  SELECT '19'UNION ALL

  SELECT '20';

  SELECT *

  FROM #tmp

  WHERE cast(left(val,case when patindex('%[^0-9]%', val) > 0 then patindex('%[^0-9]%', val) - 1 else len(val) end) as int) between 10 and 20

  ORDER BY cast(left(val,case when patindex('%[^0-9]%', val) > 0 then patindex('%[^0-9]%', val) - 1 else len(val) end) as int);

  GO

  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

  DROP TABLE #tmp;

  go

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  May 31, 2013 at 3:54 am

  #1619998

  Thanks guys.

  Will have a play over the weekend.