Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

sql query

In the attached table the column remark has the data as shown. I need to extract the part of the string after the alphabet "k" where "k" is included and in the third row it should return null as "k" is not there. Please help me with the query to fetch the data from this column. the table name is Table1.Table1
0
sam shah
Asked:
sam shah
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try..

SELECT * FROM
(
  select (CASE WHEN instr(remark,'k') > 0 THEN substr(remark,instr(remark,'k',-1)) ELSE NULL END) Extensions from YourTable
)
0
 
Pawan KumarDatabase ExpertCommented:
Testing..

WITH CTE AS
(
 
   SELECT 'ab12 k1234567' remark FROM DUAL UNION ALL
   SELECT 'cd13 k1234'  FROM DUAL UNION ALL
   SELECT 'ef14 1234'  FROM DUAL UNION ALL
   SELECT ''  FROM DUAL
)    
SELECT * FROM 
(
  select (CASE WHEN instr(remark,'k') > 0 THEN substr(remark,instr(remark,'k',-1)) ELSE NULL END) Extensions from CTE
)

Open in new window


Output



 	EXTENSIONS
1	k1234567
2	k1234
3	NULL
4	NULL

Open in new window


Hope it helps !!
0
 
sam shahAuthor Commented:
where is the table name in the above code and what is "WITH CTE AS" for? can you plz explain a bit.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Pawan KumarDatabase ExpertCommented:
That I just use for the  testing purpose. Basically creating a table at runtime..

You can use below- <<Replace CTE with your tablename>>

SELECT * FROM 
(
  select (CASE WHEN instr(remark,'k') > 0 THEN substr(remark,instr(remark,'k',-1)) ELSE NULL END) Extensions from CTE
)

Open in new window

0
 
sam shahAuthor Commented:
Thanks a lot.
0
 
Geert GOracle dbaCommented:
regexp is also a good choice ...

if you only want the numbers
with sample as (
  select 'ab12 k123567' x from dual
  union all select 'cd13 k1234' from dual
  union all select 'ef14 1234' from dual)
select regexp_substr(x, 'k(\d*)', 1, 1, 'i', 1) from sample

Open in new window


if you want k and the numbers
with sample as (
  select 'ab12 k123567' x from dual
  union all select 'cd13 k1234' from dual
  union all select 'ef14 1234' from dual)
select regexp_substr(x, 'k(\d*)') from sample

Open in new window


leaving a question open some time allows time for alternatives :)
0
 
sam shahAuthor Commented:
select remarks, regexp_substr(remarks,'\d+$') from table1;  

i used this but it is not returning the value along with "k". it is just returning the digits.
0
 
awking00Commented:
Is it possible to have more than one 'k'?
0
 
Geert GOracle dbaCommented:
\d > indicates a digit
+ indicates 1 or more 
$ indicates at the end of string

Open in new window


http://www.regular-expressions.info/refquick.html

so you want the last digits ?
your regex doesn't include a k

the k with digits if in the end of the line :  

with sample as (
  select 'ab12 k123567' x from dual
  union all select 'cd13 k5678 k1234' from dual
  union all select 'ef14 1234' from dual)
select regexp_substr(x, 'k\d+$') from sample

Open in new window


if the k and digits are not at the end of the line, then the row is not found
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now