"Truncate" String Column in another column?

Question

I have a table with a column containing String values. The String values always end with a letter "T" as the last character, a space " " and a number is right after the string:

StringColumn
"asdjadhasdT 32 asjashudT 2"
"tytweytwe aweriuhfT 23"
"ajkjsdT 6 asdajkdjkjT 1445"
"kjkasd aaassT 980"

I would like to get the number in another column.

In other words:

StringColumn                        |    ColumnValues
"asdjadhasdT 32 asjashudT 2"        |    2
"tytweytwe aweriuhfT 23"            |    23
"ajkjsdT 6 asdajkdjkjT 1445"        |    1445
"kjkasd aaassT 980"                 |    980

Gordon Linoff · Accepted Answer · 2013-07-23 02:01:30Z

2

It looks like you also have a space after the 'T'. Here is one approach:

select StringColumn, substr(StringColumn, 2 - instr(reverse(StringColumn), 'T')) as Values
from . . .

This finds the position of 'T' in the reversed string, and then takes that many characters minus two from the end of the string.

EDIT:

with t as (
     select 'asdjadhasdT 32 asjashudT 2' as StringColumn from dual union all
     select 'tytweytwe aweriuhfT 23' as StringColumn from dual union all
     select 'ajkjsdT 6 asdajkdjkjT 1445' as StringColumn from dual union all
     select 'kjkasd aaassT 980' as StringColumn from dual
)
select StringColumn,
       substr(StringColumn, 2-instr(reverse(StringColumn), 'T')) as "Values"
from t;

SQL Fiddle is here.

The problem with the first version is that Values is a reserved word in Oracle, so the query fails to compile.

edited Jul 23, 2013 at 2:01

answered Jul 23, 2013 at 1:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2607028 Over a year ago

yes, I always have a space between the number and a letter "T". I am going to check this solution. Thank you

user2607028 Over a year ago

I am sorry but is not working properly.... I have more exceptions in the table... I'm editing it right now. Thank you

DataGuy · Accepted Answer · 2013-07-23 03:45:03Z

0

i took the input strings provided and got the position of last occurence of 'T' and then took a substring below is the Query :

SELECT StringColumn,Trim(SubStr(StringColumn,INSTR(StringColumn,'T',-1 )+1)) FROM test;

Thanks, Koustubh Avadhani

edited Jul 23, 2013 at 3:45

answered Jul 23, 2013 at 3:35

DataGuy

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

"Truncate" String Column in another column?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related