how to delete words out of a string with sql?

[mokesql]

hi!

i would like to delete(replace by '') words/substrings out of a string. as example if i had the string 'my work stinks and i want to go home' and i dont know which words are in that string but i know that i have to delate the second word woh could i make it? is there a function in sql or pl/sql which make this posible? thnx for the help,

kemo

 

[THoey]

I don't know how practical this is, never know an occaision to no matter what delete the second word out of a string. Almost sounds like a homework assignment, which all of us at Tek-Tips refuse to solve. Help yes, solve no.

But, to do this you will need to use the INSTR function and the SUBSTR functions. Look these up, give it a go, and then if you still have problems with it, come back and we can help. Terry M. Hoey

 

[kindus]

I mostly agree with Terry, Anyway here is what terry is telling ....
select substr(yoursentence,1,instr(yoursentence,' '))||substr(yoursentence,instr(yoursentence,' ',1,2)+1,length(yoursentence))from dual;
Hope it meets your need.

 

[carp]

Actually, if this wasn't a homework problem, I would use the REPLACE function:

SELECT REPLACE(your_string,target_word,'') FROM my_table;

where '' is two single quotes, not one double quote.

SQL> select replace('helquacklo world','quack','') from dual;

REPLACE('HE
-----------
hello world

 

[sem]

The idea to use replace is a bit dangerous for you may replace substring belongin to the first word:
'abc ab'

You may concatenate the first word with the third and others

substr(your_word, 1, instr(your_word, ' ')) || substr(your_word, instr(your_word, ' ' , 1, 2 ) - 1)

-1 deletes extra space leaved from the second word.

 

[carp]

Sem - right you are! I reread the original post and realized I misunderstood the question. Your solution is perfect for the problem!

mokesql - if you wanted to make this more generic (for instance, if you anticipated having to delete words in specific positions on a frequent basis), you could write a function that would accept a character string and the position of the word to be deleted (in this example, the value would be 2), delete the word using sem's algorithm, and return the resultant string.