sql select in string with a split function?

Question

how can i make a select from not normed table.

i have the table like this:

enter image description here

this table is not norme BNF 2.

i just want to select the NO_ID where DEPARTEMENT = something. example:
when the input 44 then NO_ID =1
when the input 37 then NO_ID =3
when the input 13(not in table) then NO_ID = 5

of course when input = 44 it works:

SELECT [NO_ID]    
FROM [T_TARIF_ZONE]
WHERE DEPARTEMENT = '44'

but how can i put in WHERE statement when the input = 37 or 13.

thanks you in advance, Stev

I think you need to get NO_ID values where DEPARTMENT number exists in DEPARTMENT number string? — IndoKnight
– IndoKnight, Commented Apr 19, 2013 at 8:39

Pavel · Accepted Answer · 2013-04-19 08:28:49Z

1

try this:

SELECT [NO_ID]    
FROM [T_TARIF_ZONE]
WHERE DEPARTEMENT like '%37%'

answered Apr 19, 2013 at 8:28

Pavel

1,64715 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1958628 Over a year ago

thanks you, yes you right, but how can i retrun 5 when the value is not in Departement example 13

Hans Keﬆing Over a year ago

This would fail if there is a '371' code in there - I don't know if that applies here.

TechDo · Accepted Answer · 2013-04-19 09:02:14Z

1

Please try:

select 
    NO_ID, 
    DEPARTMEMT 
from 
    T
where 
    ' '+DEPARTMEMT+' ' like 
    (case when @var=13 then ' FRANCE ' ELSE '% '+@var+' %' END)

answered Apr 19, 2013 at 9:02

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Comments

IndoKnight · Accepted Answer · 2013-04-19 08:36:51Z

0

Oracle version -

SELECT [NO_ID]    
FROM [T_TARIF_ZONE]
WHERE INSTR(DEPARTEMENT, '44')>0  OR INSTR(DEPARTEMENT, '37')>0 OR INSTR(DEPARTEMENT, '13')>0

SQL Server version -

    SELECT [NO_ID]    
FROM [T_TARIF_ZONE]
WHERE PATINDEX('44', DEPARTEMENT)>0  OR PATINDEX('37', DEPARTEMENT)>0 OR PATINDEX('13', DEPARTEMENT)>0

edited Apr 19, 2013 at 8:36

answered Apr 19, 2013 at 8:31

IndoKnight

1,8641 gold badge21 silver badges31 bronze badges

Collectives™ on Stack Overflow

sql select in string with a split function?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related