obtain a substring from the result set

[douglevin]

Hi, I have a query that returns the concatenation of two strings from two separate tables. 'site' and 'clicktrail'

SELECT CONCAT(a.file_prefix, b.file_name) as filePrefix from site a, clicktrail b 
WHERE a.id = b.site_id 
AND a.name='Unique Name'

say it returns the following

filePrefixA_
filePrefixB_
filePrefixC_

I need to remove the underscore from the end of each row, so the result would be

filePrefixA
filePrefixB
filePrefixC

I've been playing around with the SUBSTRING function, but I can't get the correct results, any help is greatly appreciated.

[amitpatel66]

Hi, I have a query that returns the concatenation of two strings from two separate tables. 'site' and 'clicktrail'


SELECT CONCAT(a.file_prefix, b.file_name) as filePrefix from site a, clicktrail b
WHERE a.id = b.site_id
AND a.name='Unique Name'

say it returns the following

filePrefixA_
filePrefixB_
filePrefixC_

I need to remove the underscore from the end of each row, so the result would be

filePrefixA
filePrefixB
filePrefixC

I've been playing around with the SUBSTRING function, but I can't get the correct results, any help is greatly appreciated.

Use REPLACE function as shown below:

SELECT REPLACE(CONCAT(a.file_prefix, b.file_name),'_','') as filePrefix from site a, clicktrail b 
WHERE a.id = b.site_id 
AND a.name='Unique Name'