All Forums Database
amadu 14 posts Joined 09/14
14 Sep 2015
SUBSTRING and CONCATENATION

Hi There,
i need help to write an sql script to return values from different positions within the example string below:
#12345678.#98B9876.#123456
i want values from positions 2-8 and concatenate with values in positions 11-13.
is there a way to return the values from both positions using sbstr function only or i have to concatenate for it to work?
the subtr function i used is able to get the values from positions 2-8 but i havent been able to concatenate with the values from positions 11-13.
Below is my working subtr function:
SELECT SUBSTR(FIELD, 2,8)
FROM DB.TABLE_NAME

 
 

best regards.
 

Amadu Barrie
manib0907 61 posts Joined 04/15
14 Sep 2015

SELECT SUBSTR(FIELD, 2,8)||SUBSTR(FIELD, 11,13)
FROM DB.TABLE_NAME
Try this

Cheers,
Mani

amadu 14 posts Joined 09/14
16 Sep 2015

Hi Mani.
thanks for your help - works perfectly.

Amadu Barrie

amadu 14 posts Joined 09/14
16 Sep 2015
Hi Mani et al,

What I want to do now is to do a count over my resultset.

The distinct function below is working (but I want something to tell me the number of occurrence of the duplicates):

SELECT DISTINCT SUBSTR(field, 2,7)||SUBSTR(field, 11,3)              
FROM  DB.TABLE                   
WHERE FIELD = 'XX'                      
                              

 

Amadu Barrie

saravanatn 10 posts Joined 07/11
16 Sep 2015

Hi,
To get the count :

SELECT SUBSTR(field, 2,7)||SUBSTR(field, 11,3), COUNT(*)   
FROM  DB.TABLE                  
WHERE FIELD = 'XX' 
GROUP BY 1;

 

amadu 14 posts Joined 09/14
17 Sep 2015

Thank you Saravanath.
much appreciated.

Amadu Barrie

You must sign in to leave a comment.