CODEWITHSUNDEEP
sqloracle-databaseplsql
user2607028
user2607028

Reputation: 345

"Truncate" String Column in another column?

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

Upvotes: 0

Views: 998

Answers (2)

DataGuy
DataGuy

Reputation: 1

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Related Questions