Parse String ---help needed urgent!! 2

[Yiyi]

I have question about the substring in sql, need help urgent!

how to parse a string with format "string1/string2/string3" base on /,
so that I can use in where clause like:

select .....

from...

where field1=string1
field2=string2
field3=string3

Has sql have these function?

Appreciate for your help!

 

[THoey]

Please realize that this is untested:

WHERE Field1 = SUBSTR(TempStr, 1, INSTR(w_desc, '/') - 1) AND
      Field2 = SUBSTR(TempStr, INSTR(w_desc, '/') + 1, INSTR(w_desc, '/', 1, 2) - 1) AND
      Field3 = SUBSTR(TempStr, -1, INSTR(w_desc, '/') + 1)

TempStr is the String you are parsing.

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Yiyi]

Thanks! th3856.

But looks like INSTR doesn't work. I can not excute the SQL statement with INSTR? ANy suggestion?

Thanks!

By the way, how do I know how '/' in the string. Is there any these function?

 

[Yiyi]

Sorry , it is :
how do I know how many '/' in the string?

 

[Yiyi]

Sorry, let me clarify this question more clearly,

for example, I have two tables: table A and B, table A has a column (name: Book) with value formate like "string1/string2/string3" . Then I need to do some join to get some data from table B:

select something

from A,B

when B.field=string1(in A.Book substring)
B.field1=string2(in A.Book substring)
B.field2=string3(in A.Book substring)

How can I do by calling some SQL function?

And how can I know how many '/' in A.Book string becasue some time it would be in only 'string1/string2'
formate.

Appreciate your great help!

 

[karluk]

Please let us know which database you are using. Terry's suggestion was specific to Oracle. I'm fairly sure it won't work in SQL Server.

 

[tlbroadbent]

How about a different approach? Rather than parsing the string from table A, how about concatenating the strings in table B.

[tt]Select B.*
From A, B
Where B.field1 + '/' + B.field2 + '/' + B.field3 = A.Book
Or B.field1 + '/' + B.field2 = A.Book
Or B.field1 = A.Book[/tt]

This will handle from 1 to 3 strings. If you have more, you can expand this query. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Yiyi]

Thank you very much, guys!

I used Terry's idea, and it works great!

FYI, I am using informix, and they don't support INSTR.