CODEWITHSUNDEEP
mysqltrim
Shawn Benson
Shawn Benson

Reputation: 137

Using the trim function to narrow down results set

I need to gather data from two columns, concat them so that it's only the first six of the first column and the last six of the second column, separated by ' + '. Some have been input with weird spaces in front or in back, so we must also use the trim feature and get rid of all NULL. I haven't had any issues with the first part, but am struggling to use the trim feature in a way that gives the desired output.
Output needs to look like this:

neededoutput

Input Data sample:

sample

The following code returns results, but the output doesn't match so I know the trim is wrong:

SELECT CONCAT(SUBSTRING(baseball, 1, 6), ' + ',
SUBSTRING(football, -6)) AS MYSTRING 
FROM datenumtest2  
WHERE baseball IS NOT NULL AND football IS NOT NULL;

I also tried the following, but get an error message about the parameters being incorrect:

SELECT CONCAT(SUBSTRING(LTRIM(baseball, 1, 6)), ' + ',
SUBSTRING(RTRIM(football, -6))) AS MYSTRING 
FROM datenumtest2
WHERE baseball IS NOT NULL AND
football IS NOT NULL;

I'm still new to this site and learning, but I have tried to include as much as I can! If there is other information that I can add to help, please let me know.

Upvotes: 1

Views: 23

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You just need to use Trim() on the column(s), before using Substring() function on them:

SELECT CONCAT(SUBSTRING(TRIM(baseball), 1, 6), ' + ',
              SUBSTRING(TRIM(football), -6)) AS MYSTRING 
FROM datenumtest2  
WHERE baseball IS NOT NULL AND 
      football IS NOT NULL;

Upvotes: 1

Related Questions