CODEWITHSUNDEEP
mysqlsql
user19820140
user19820140

Reputation:

Column Value Split and Split value create new value

SQL Database in Table in many Columns is created. One Column record the value of Two types of data in one Record. How to Split this Record into Columns. In this Column only the record is Like '%WEB%' then Split '-' this Symbol. Otherwise Null.

My Table Column Record like : WEBNOTE-053122, WEBEVV-121222, OCM-TIXOC2, OCM-LNTHEOCM

In this Record in I want one Column in WEBLIVE, WEBREC, OCM-TIXOC2, OCM-LNTHEOCM and The second Column in 053122, 121222, or this Second Column in Like '%OCM%' record is null

Upvotes: 0

Views: 42

Answers (1)

Jonas Metzler
Jonas Metzler

Reputation: 5975

If I understand correct, you want to use SUBSTRING_INDEX to split your string in the part before the "-" and the rest.

And use CASE WHEN to cover your two cases.

Something like this:

SELECT
CASE WHEN yourcolumn LIKE '%WEB%' 
  THEN SUBSTRING_INDEX(yourcolumn,'-',1) 
  ELSE yourcolumn END AS column1,
CASE WHEN yourcolumn LIKE '%WEB%'
  THEN SUBSTRING_INDEX(yourcolumn,'-',-1) END AS column2
FROM yourtable;

Or if you want to point out in your query that the second column should be NULL if the string doesn't contain "WEB", you could add an ELSE clause:

SELECT
CASE WHEN yourcolumn LIKE '%WEB%' 
  THEN SUBSTRING_INDEX(yourcolumn,'-',1) 
  ELSE yourcolumn END AS column1,
CASE WHEN yourcolumn LIKE '%WEB%'
  THEN SUBSTRING_INDEX(yourcolumn,'-',-1) 
  ELSE NULL END AS column2
FROM yourtable;

Your sample input:

yourcolumn
WEBNOTE-053122
WEBEVV-121222
OCM-TIXOC2
OCM-LNTHEOCM

The result of the query:

column1 column2
WEBNOTE 053122
WEBEVV 121222
OCM-TIXOC2
OCM-LNTHEOCM

Try out: db<>fiddle

Upvotes: 1

Related Questions