|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I am Selecting 2 columns and concatenate the same in my result .
Three columns are
1) Company_code 4 bytes(char)
2) BUSINESS_CODE (20) bytes char
3) Resultant column - concat of company_code ||'.'|| business_code
business_code will have 'XYZ' , 'ABC'.
company_code will have 'YYY', 'EEE' like .
My result column should limit to 10 bytes and also i should not get any space in concatenation result .
In below SQL Query ,
| Code: |
Select COMPANY_CODE,BUSINESS_CODE, COMPANY_CODE||'.'||BUSINESS_CODE
FROM TEST_TABLE
WITH UR
Result shows
YYY, XYZ , YYY .XYZ
but i need
YYY,XYZ,YYY.XYZ no space before '.'
|
Trim will work on this? and also would like to limit length of the result to 10 bytes . How we can achieve?
Thanks
Balaji K |
|
| Back to top |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
Why don't you use TRIM and SUBSTR?
| Code: |
Select COMPANY_CODE,BUSINESS_CODE, SUBSTR(TRIM(COMPANY_CODE)||'.'||BUSINESS_CODE,1,10)
FROM TEST_TABLE
WITH UR |
. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| It doesn't make sense, why you need a truncated data? That's totally incorrect. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| RahulG31 wrote: |
Why don't you use TRIM and SUBSTR?
| Code: |
Select COMPANY_CODE,BUSINESS_CODE, SUBSTR(TRIM(COMPANY_CODE)||'.'||BUSINESS_CODE,1,10)
FROM TEST_TABLE
WITH UR |
. |
Sure Rahul, Thanks for the help. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Rohit Umarjikar wrote: |
| It doesn't make sense, why you need a truncated data? That's totally incorrect. |
Rohit,
So far we have data in prod doesn't exist more than 3 chars but still variable is defined more than of it . I need to populate the combination of this two which is passed to downstream applications and they have some restrictions to receive the same with some fixed byte length at present . Logic will change over the time for sure. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Okay, Why was it so difficult for you to know STRIP/TRIP/REPLACE/SUBSTRING functions by doing little research?
btw , You will need to TRIM both the columns. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Rohit Umarjikar wrote: |
Okay, Why was it so difficult for you to know STRIP/TRIP/REPLACE/SUBSTRING functions by doing little research?
btw , You will need to TRIM both the columns. |
Yes Rohit ,it should have been done from my end on this post . First thing in my mind was TRIM . I used to post my queries always with my research or finding before seeking assistance or help . But this post i didn't do that and i apologize for the same . |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| All right, hope all this solutions worked for you. |
|
| Back to top |
|
|
|
|
