SQL id field with 3 values, I need to access each of them-CodePudding

This is the first time I have come across a situation like this. I have an id field with 3 different values. I have been asked to extract each of them for later use, but I don't know how to create query.

table

id - TJ11|X033|27636

TJ11 is a sku

X033 is a store

27636 is a car id

I try with

SELECT table.id FROM table
WHERE table.id like 'TJ11'

I need each value to be able to display it in the JSP.

CodePudding user response：

Based on answer from how-to-split-the-name-string-in-mysql,I made a similar answer to you

SET @STR = 'TJ11|X033|27636';

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(@STR, '|', 1), '|', -1) AS sku,
   If(  length(@STR) - length(replace(@STR, '|', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(@STR, '|', 2), '|', -1) ,NULL) 
           as store,
   SUBSTRING_INDEX(SUBSTRING_INDEX(@STR, '|', 3), '|', -1) AS car_id

DB Fiddle Demo