CODEWITHSUNDEEP
mysqlsqlstringsubstring
Sinister Beard
Sinister Beard

Reputation: 3680

Check for a substring in MySQL and return a value based on it

I have an SQL query that returns the following values:

BC - Worces
BC Bristol
BC Central
BC Torquay
BC-Bath
BC-Exeter
BC-Payroll

So, we have some BC with just a space, some with a dash and some with a dash with spaces on either side. When returning these values, I want to replace any of these BC variants with "Business Continuity: " followed by Bath, or Exeter etc.

Is there a way of checking what value is returned and (I'm assuming in a separate column) returning a field based on it? If every iteration was the same, I could just use Trim, but it's the variation that's throwing me out.

Upvotes: 0

Views: 394

Answers (3)

tmutton
tmutton

Reputation: 1101

*Not tested

select 'Business Continuity:' + SUBSTRING(REPLACE(fieldname, 'BC - ', 'BC-'), 4) from table;
  1. Replaces all instances of 'BC - ' with 'BC-'.
  2. Substrings everything from 4 characters onwards ie 'BC-' and 'BC '

Upvotes: -1

Fred
Fred

Reputation: 5808

You could use a case on the select 

CASE WHEN Left(`colname`, 5) = 'BC - ' THEN CONCAT('Business Continuity: ', SUBSTRING(`colname`, 6))
     WHEN Left(colname, 3) = 'BC ' THEN CONCAT('Business Continuity: ', SUBSTRING(`colname`, 4))
     WHEN Left(`colname`, 3) = 'BC-' THEN CONCAT('Business Continuity: ', SUBSTRING(`colname`, 4))
     ELSE `colname`
END as `colname`

Upvotes: 1

Sameer Mirji
Sameer Mirji

Reputation: 2245

You can use REPLACE function along with CASE statement for this.

SELECT CASE 
    WHEN `col_name` LIKE 'BC %' THEN REPLACE(`col_name`, 'BC ', 'Business Continuity: ') 
    WHEN `col_name` LIKE 'BC-%' THEN REPLACE(`col_name`, 'BC-', 'Business Continuity: ')
    ELSE `col_name`
    END as `col_name` FROM `table_name`;

Upvotes: 1

Related Questions