SUBSTRING and CONCATENATION

Database
N/A

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.


5 REPLIES

Re: SUBSTRING and CONCATENATION

SELECT SUBSTR(FIELD, 2,8)||SUBSTR(FIELD, 11,13)

FROM DB.TABLE_NAME

Try this

N/A

Re: SUBSTRING and CONCATENATION

Hi Mani.

thanks for your help - works perfectly.

N/A

Re: SUBSTRING and CONCATENATION

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'                     
                             

Re: SUBSTRING and CONCATENATION

Hi,

To get the count :

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

Re: SUBSTRING and CONCATENATION

Thank you Saravanath.

much appreciated.