CODEWITHSUNDEEP
mysqlsqlvarchar
Emily Bell
Emily Bell

Reputation: 11

TRIM query on varying character length varchar

Help!

I am trying to trim the below string to the middle section "MS Lync"

"Systems & Apps -> MS Lync -> Request for Change"

I have tried the following query but I still get the special characters. The string lengths change but the special characters dont:

LEFT(probcodedesc, CHARINDEX('-', probcodedesc)) AS Area,
substring(probcodedesc, charindex('> ',probcodedesc),charindex('> ', probcodedesc, charindex('>', probcodedesc) - charindex(' ->', probcodedesc))) as Category,
RIGHT(probcodedesc, CHARINDEX('>', REVERSE(probcodedesc))) AS Event

All help & advice appreciated!

Upvotes: 1

Views: 129

Answers (1)

spencer7593
spencer7593

Reputation: 108370

I'd use the nifty SUBSTRING_INDEX function for doing something like this.

Reference: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring-index

For example:

SET @s = 'Systems & Apps -> MS Lync -> Request for Change';

SELECT TRIM(SUBSTRING_INDEX(@s,'->',1)) AS `Area`

Area
----------------
Systems & Apps  


SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@s,'->',-2),'->',1)) AS `Category`

Category       
---------
MS Lync  


SELECT TRIM(SUBSTRING_INDEX(@s,'->',-1)) AS `Event`

Event
------------------
Request for Change

Upvotes: 1

Related Questions