1

I dont want to use any function or any procedure.

I want simple select query to check the existance of the each part of string.

like i have one table dummy which have name column 

                         Id     name 
                          1   as;as;as
                          2    asd;rt

and child table 

                       child_id       name
                          23           as
                          24           asd
                          25           rt

so any i can do that

i have tried like 

select substr(first_name,1,instr(first_name,';')-1) from dummy;
select substr(first_name,instr(first_name,';')+1,instr(first_name,';')-1) 
                                                               from dummy;

Which is giving only first/second part but other part how to get other part

valex
  • 23,966
  • 7
  • 43
  • 60
user1111351
  • 81
  • 1
  • 1
  • 7
  • What RDBMs you are using? SQL Server?MySQL? ORacle? etc.. – John Woo Jan 17 '13 at 13:18
  • If you're using MySQL, this may be a duplicate: http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query – Neville Kuyt Jan 17 '13 at 13:42
  • Why the limitation on using functions? If you need to expand a delimited string into a series of values a function is the best way to do it. – JNK Jan 17 '13 at 13:48
  • What is the desired output? Do you need to separate your first name or what? – Art Jan 17 '13 at 18:14

2 Answers2

0

If I've got it right - You need to join these tables if child's NAME is included in a DUMMY.Name

SQLFiddle example

select t1.*, 
       t2.child_id, 
        t2.name as t2name 
from t1
left join t2 on (';'||t1.name||';' like '%;'||t2.name||';%')
valex
  • 23,966
  • 7
  • 43
  • 60
0

I would need more information on this question. We do not know if you have to detect more than one of the possible strings on just one field.

You could use three like clauses for the three possible scenarios

  1. LIKE column_name ||'%;'
  2. LIKE '%;'|| column_name
  3. LIKE ';%'|| column_name ||'%;'

But it would probably work better for the future learning about building regular expressions. Here is a webpage that helped me a lot: txt2re.com

listik
  • 229
  • 1
  • 5