All Forums Database
praveenyv 1 post Joined 04/14
23 Apr 2014
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.
 

dnoeth 4628 posts Joined 11/04
23 Apr 2014

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

Dieter

kumar_abhilash 15 posts Joined 04/14
26 Apr 2014

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

Adeel Chaudhry 773 posts Joined 04/08
26 Apr 2014

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

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

-- If you are stuck at something .... consider it an opportunity to think anew.

divyagolla 22 posts Joined 02/14
10 Dec 2014

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

Raja_KT 1246 posts Joined 07/09
10 Dec 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
10 Dec 2014

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

Dieter

divyagolla 22 posts Joined 02/14
14 Jan 2015

Thank you Dieter and Raja .

divyagolla 22 posts Joined 02/14
19 Feb 2015

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.

ravimans 54 posts Joined 02/14
24 Feb 2015

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?

divyagolla 22 posts Joined 02/14
25 Feb 2015

Hi Ravi, i provided some details here, let me know if you need any details
Table A
Product_nm Product_id Ar_CD offercode
A 10 2 10
A 10 3 10
B 11 17 11
B 11 18 11
B 11 19 11
Table B
Product_nm Product_id Ar_CD offer_code
A 10 2 8
A 10 3 8
B 11 17 1
B 11 18 1
B 11 20 1
I need data like below
Product_nm Product_id offer_code
A 10 10
B 11 1
When Ar_cd for each product (table_a) is subset of table B
i.e, for A - > Ar_CD (2,3) from Table A are present in Table B , so offcode should come as 10
for B, 17,18 are matching but 19 is not present in table B so offer_code should come as 1
How to implement this in where clause i.e, checking subset of data

Rohan_Sawant 55 posts Joined 07/14
25 Feb 2015

Hi dvya,
 
I am not clear with the requirement since I feel there are many more CASES for which requirement has not been mentioned.
Like is the PRODUCT_ID would be same for same PRODUCT_NM? Whats the result when B has offer code (17,18,19,20)? According to my understanding I have done the following and hope your requirement is met:
 

/* Creating test data */
CREATE MULTISET VOLATILE TABLE VT_TABLE_A ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
  PRODUCT_NM VARCHAR(1),
  PRODUCT_ID INTEGER,
  AR_CD INTEGER,
  OFFERCODE INTEGER
)
PRIMARY INDEX (PRODUCT_NM)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TABLE_A VALUES ('A',10,2,10);
INSERT INTO VT_TABLE_A VALUES ('A',10,3,10);
INSERT INTO VT_TABLE_A VALUES ('B',11,17,11);
INSERT INTO VT_TABLE_A VALUES ('B',11,18,11);
INSERT INTO VT_TABLE_A VALUES ('B',11,19,11);

CREATE MULTISET VOLATILE TABLE VT_TABLE_B ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
  PRODUCT_NM VARCHAR(1),
  PRODUCT_ID INTEGER,
  AR_CD INTEGER,
  OFFERCODE INTEGER
)
PRIMARY INDEX (PRODUCT_NM)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TABLE_B VALUES ('A',10,2,8);
INSERT INTO VT_TABLE_B VALUES ('A',10,3,8);
INSERT INTO VT_TABLE_B VALUES ('B',11,17,1);
INSERT INTO VT_TABLE_B VALUES ('B',11,18,1);
INSERT INTO VT_TABLE_B VALUES ('B',11,20,1);
/* Completed creating test data */

/* Your output */
SELECT
  A.PRODUCT_NM
, A.PRODUCT_ID
, CASE
	WHEN A.CNT = COALESCE(B.CNT,0)
	THEN A.OFFERCODE
	ELSE B.OFFERCODE
  END AS OFFERCODE
FROM
(
	SELECT
	  A.PRODUCT_NM
	, A.PRODUCT_ID
	, A.AR_CD
	, A.OFFERCODE
	, COUNT(A.AR_CD) OVER (PARTITION BY A.PRODUCT_NM,A.PRODUCT_ID) AS CNT
	FROM
	  VT_TABLE_A A
	INNER JOIN
	  VT_TABLE_B B
	ON A.PRODUCT_NM = B.PRODUCT_NM
	AND A.PRODUCT_ID = B.PRODUCT_ID
	AND A.AR_CD = B.AR_CD
) A
LEFT OUTER JOIN
(
	SELECT
	  PRODUCT_NM
	, PRODUCT_ID
	, AR_CD
	, OFFERCODE
	, COUNT(AR_CD) OVER (PARTITION BY PRODUCT_NM,PRODUCT_ID) AS CNT
	FROM
	  VT_TABLE_B
) B
ON A.PRODUCT_NM = B.PRODUCT_NM
AND A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY 1,2,3;

 
Thanks,
Rohan Sawant

You must sign in to leave a comment.