Dynamic CASE expression

Question

Is there anyway to have a case expression that produces different results based upon a value being an integer or a character.

Tables

  ID        CODE
  1         ABC
  2         123
  3         YHU
  4         456
  5         ikl

I was looking for an expression that separated the int and char.

Result e.g.

  ID        CODE    Category
  1         ABC       Char
  2         123       Int
  3         YHU       Char
  4         456       Int
  5         ikl       Char

my general logic

 CASE WHEN CODE = INT THEN 'Int' Else 'Char' end as Category

But i didnt know if this was possible in SQL?

I am looking mainly for a way to recognise whether its int or char

UPDATE:

What is the best way to separate the numbers from char, * and - into 2 different categories using case expression

  ID        CODE    Category
  1          *        No_NUM
  2         123       NUM
  3         YHU       No_NUM
  4         456       NUM
  5         ikl       No_NUM
  6          -        No_NUM

Amit Kumar Singh · Accepted Answer · 2017-10-13T14:08:44.173

2

You can use SQL ISNUMERIC function.

SELECT ID, CODE, CASE ISNUMERIC(CODE) WHEN 1 THEN 'NUM' ELSE 'No_NUM' END AS Category FROM my_table;

Another Variation with REGEX

SELECT ID, CODE, CASE WHEN CODE LIKE '%[0-9]%' THEN 'NUM' ELSE 'No_NUM' END AS Category FROM my_table;

edited Oct 13 '17 at 14:08

answered Oct 13 '17 at 14:02

Amit Kumar Singh

4,393
2
9
22

Lukasz Szozda · Answer 2 · 2017-10-13T14:04:42.220

0

You could use TRY_CAST (SQL Server 2012+)

SELECT *,
   CASE WHEN TRY_CAST(CODE AS INT) IS NOT NULL THEN 'Int' ELSE 'Char' END
FROM tab;

I've assumed that column is NOT NULL.

Rextester Demo

EDIT:

It is just text inside CASE:

SELECT *,
   CASE WHEN TRY_CAST(CODE AS INT) IS NOT NULL THEN 'NUM' ELSE 'No_NUM' END
FROM tab;

Rextester Demo 2

edited Oct 13 '17 at 14:04

answered Oct 13 '17 at 13:57

Lukasz Szozda

162,964
23
234
275

could you have a look at my update – Ryan Gadsdon Oct 13 '17 at 14:03

score 0 · Answer 3 · answered Oct 13 '17 at 14:09

Use PATINDEX:

 create table #temp ([ID] int, [CODE] nvarchar(5))

 insert into #temp values (1, '*')
 insert into #temp values (2, '123')
 insert into #temp values (3, 'YHU')
 insert into #temp values (4, '456')
 insert into #temp values (5, 'ikl')

 Select ID
    , CASE  when PATINDEX('%[0-9]%', [code]) = 1 then 'num'
         -- when PATINDEX('%[^0-9]%', [code]) = 1 then 'no_num'
         -- when PATINDEX('%[A-Z]%', [code]) = 1 then 'char' 
         -- when PATINDEX('%[^A-Z]%', [code]) = 1 then 'no_char' /*etc..*/
      ELSE 'no_num' END AS 'Category'
 from #temp

Dynamic CASE expression

3 Answers3