Remove the first 4 characters in a string

Database

Remove the first 4 characters in a string

Hi,

I have data in the following way:

State

110-California

120-New York

130-Washington

I need to select only the Name of the State and exclude the first 4 characters. I tried to use Right and SubStr functions. But could not get it to work accurately. Please help.

11 REPLIES
N/A

Re: Remove the first 4 characters in a string

There's no RIGHT function in Standard/Teradata SQL, but with SUBSTR[ING] it's easy:

SUBSTRING(state FROM 5) -- Standard SQL
or

SUBSTR(state, 5) -- non-Standard SQL

Re: Remove the first 4 characters in a string

SELECT SUBSTR(STATE,5) FROM <TABLE> ;

Teradata Employee

Re: Remove the first 4 characters in a string

Or for a generic solution [in case '-' can come in data at any other length] .... you can use:

SELECT SUBSTR(state, INDEX(state, '-') + 1)

Re: Remove the first 4 characters in a string

I have table with follwing data

EMP_ID EMP_ADDR

11010 Divya is from bangalore

12034 Suresh from chennai

150567 from mumbai

12345 Sadic is working from delhi

I have to extract the EMP_ADDR like starting from "FROM" till end

Sample O/P

EMP_ID EMP_ADDR

11010 from bangalore

12034 from chennai

150567 mumbai

12345 from delhi

Can some one please help me to get required out put

Re: Remove the first 4 characters in a string

Which TD version you are in?

One way is :You can think of using regexp_instr to get the position of 'from'. Then use subtsr.

N/A

Re: Remove the first 4 characters in a string

SUBSTRING(EMP_ADDR FROM POSITION('FROM' IN EMP_ADDR))

Re: Remove the first 4 characters in a string

Thank you Dieter and Raja .

Re: Remove the first 4 characters in a string

I had a scenario in which entire data from column A from table A should match with Column A from table B

(Select Offerid , product_id, apc_code from table_A) live_offer

Innerjoin 

(Select Offerid , product_id, apc_code from table_A) plan_offer

on live_offer.product_id = plan_offer.product_id

Where apc_code = apc_code

In the where condition i have check all the apc_codes that are present in table_a(plan offer) should present in table_b(live_offer)

 

say table a is having 2,4 and table b is hvaing 2,4,6 then i will take offrer id from tabke a .

in future table a may get 2,3 as table b doesnt have 3 i have to offer id from table b

Please help us with the query in where clause. Thanks in Advance.

Re: Remove the first 4 characters in a string

Hi Dvya,

I am not able to understand your question? Are you saying if there is matching offerid in table a and b, it should return only those records?