This content has been marked as final. Show 4 replies
Lucy Discover wrote:Sorry, it's unclear what you're asking.
I know this question could be so basic. I am just supposed to Update a column that looks like
with just the 5 numeric digits found at the end. some of the Data in the column has Space inbetween the STRING. I want to truncate the space and update it with somthing like substr(stringname,3,5) ... So what do i do to truncate Space in the data. I tried (trim(chr(32) from string_name and it did not return the desired results.
To get the last 5 characters of stringname:
To get the group of digits at the end of the string, regardless of how many there are:
SUBSTR ( stringname , -5 )
Whenever you have a problem, it helps if you post a little sample data (CREATE TABLE and INSERT statments) and the results you want from that data.
REGEXP_SUBSTR ( stringname , '[0-9]+$' )
If you know it's always the last 5 characters of the string you could use:1 person found this helpful
If you want to remove spaces you can use:
SQL> ed Wrote file afiedt.buf 1 with t as (select '*KA12345' as txt from dual union all 2 select '*KA 12345' from dual) 3 -- 4 select substr(txt,-5) as num 5* from t SQL> / NUM ----- 12345 12345 SQL>
SQL> ed Wrote file afiedt.buf 1 with t as (select '*KA12345' as txt from dual union all 2 select '*KA 12345' from dual) 3 -- 4 select replace(txt,' ') as space_free 5* from t SQL> / SPACE_FRE --------- *KA12345 *KA12345 SQL>
1 person found this helpful
with data as ( select '*KA12345' fld from dual union all select '*KA 12345' from dual union all select '*KA 123 45' from dual ) select fld, regexp_replace(fld,'[ ]+') result from data; --- FLD RESULT --- *KA12345 *KA12345 --- *KA 12345 *KA12345 --- *KA 123 45 *KA12345
Thank you Frank .. Thank you all ...