Get value in the field after a character

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Thu, 12 Jul 2007 3:22 pm

Good afternoon!
I need to get a given, but in the field have other information recorded, and what \"separates\" such information is a comma. Want to get only the word after the comma.
So: ' example, I want to test only the value ' test ' ... The content until the comma is variable, so I can't use the substr.

Please, how can I do?
: roll: thank you!
Location: São Paulo - SP

Poston Thu, 12 Jul 2007 3:26 pm

Ah! One more thing: can't use function, cuz I'm creating an alert (ERP) and there only accepts the simple select ... I need to make the \"treatment\" in the same field.
Location: São Paulo - SP

Poston Thu, 12 Jul 2007 3:37 pm

Ravindra, try this code:
Code: Select all
  v_string VARCHAR2(50) := 'Paulo;Trevisolli';
  dbms_output.put_line(' Depois do ponto e virgula : ' || SUBSTR(v_string,INSTR(v_string,';')+1,11));
qualquer thing sends ai.
Location: Araraquara - SP


OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional

Poston Thu, 12 Jul 2007 3:39 pm

Speaks thatifc, good afternoon, so make a selequete of the column that you need!!

Create a spool c: este.txt in sqlplus and then abrar in excel, separating by commas on the Tools menu, import external data, you put the daee separator \",\": lol: Any stuff, post here in the forum!

Abs Norry
Location: São Paulo

O mundo gira muito!!

Poston Thu, 12 Jul 2007 3:45 pm

Trevisolli and Norry, muito thanks for the comeback!!
Here is the way I found to solve this pendenga:
Code: Select all
Select Substr('Exemplo,Teste', Instr('Exemplo,Teste', ',')+1)
  From Dual
Talal: wink:
Location: São Paulo - SP

Poston Thu, 05 Dec 2013 1:38 pm

I'm with a doubt quite similar: in your case you sought the second item in the " " list, but if I had more than two items and wanted to grab only the second? Because the way you have done it will return everything after the second.

Code: Select all
Select Substr('Exemplo,Teste, Exemplo2', Instr('Exemplo,Teste,Exemplo2', ',')+1)
  From Dual
this will return: " test, Example2 " how could I do to get only the test?

Poston Thu, 05 Dec 2013 5:35 pm

ironijunior, simply adjust the string gainer, follows, for example:
Code: Select all
SQL> SELECT SUBSTR('Exemplo,Teste,Exemplo2',+9,5) FROM DUAL;


Location: Brasília

Adriano Alves
---Para cada problema dificil existe uma solução simples.----

  • See also
    Last Post

    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 4 guests