0

I have a requirement to get string present inside single quotes in PL/SQL, either using regex or any other method whichever works. 

example: 'He is my uncle's son.and he is a programmer' 
output: He is my uncle's son.and he is a programmer

Thanks in advance!!

Karishma Sahu
  • 39
  • 3
  • Considered the use of `trim(,'''')`? And if you're on an older version `ltrim(rtrim(,''''),'''')`? – Radagast81 Sep 11 '18 at 12:40
  • What about a string like 'a single quote '' '? What should the result be? Also, can you have strings like 'here starts the quoted part: '' QUOTED '''? – Aleksej Sep 11 '18 at 13:00
  • in Oracle when we want to put a single quote in a string then we write it like (' I am going to see "The Palace" ') so the expected output is ( I am going to see 'The Palace' ) so My requirement is also same I just want to get in the same way. – Karishma Sahu Sep 11 '18 at 15:19
  • Could your string contain a substring that contains a single quote that you would want to preserve? Like: 'Mr. O'Brien was his name'? – Gary_W Sep 11 '18 at 21:55
  • @Gary_W : Yes it should be preserved. – Karishma Sahu Sep 12 '18 at 13:33
  • For a better answer and to make it easier to help you, you should edit your original post to include sample strings of what the possible data could look like. Embedded quotes, the string at the start with characters following, at the end, etc. Use real data examples if possible for the best accuracy. Show what you've already tried as well. – Gary_W Sep 12 '18 at 13:59
  • My requirement is, I need to convert string into ASCII value but not special characters. so suppose my variable\column has a string value stored in it then I need to convert all the word into ASCII but I don't want to covert ' quote into ASCII..thats why I am looking for a function which can check if any string is present inside single quotes. In my case, I am considering a complete sentence as a string. – Karishma Sahu Sep 14 '18 at 12:53
  • I wonder if you solved the issue. Did you try `SELECT REGEXP_SUBSTR('''He is my uncle''s son.and he is a programmer''', '''(.*)''', 1, 1, NULL, 1) AS Result FROM DUAL`? – Wiktor Stribiżew Sep 25 '19 at 14:39

1 Answers1

0

Use can use Replace function..

select replace ('<some text here>',chr(39)) from dual;

example select replace (q'[this is ' having' mult'iple ' quotes'']',chr(39)), q'[this is ' having' mult'iple ' quotes'']' original_text from dual;

output will be :

this is having multiple quotes

This will remove all the single quotes in the string.

If you want to remove double quotes then user chr(34).

Chaitanya Kotha
  • 466
  • 2
  • 4
  • Thanks for your response but I don't want to remove single quotes I want the string which is inside single quote without removing single quotes then that string will be sent inside a loop. – Karishma Sahu Sep 11 '18 at 15:02
  • The single quotes in the column will not be removed... The function will return the string by removing single quote... – Chaitanya Kotha Sep 11 '18 at 15:05