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

Question

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.

You may need to check this [how-to-split-the-name-string-in-mysql](https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) — flyingfox, Oct 06 '22 at 13:01
Does this answer your question? [Split value from one field to two](https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two) — Aarlaneth, Oct 06 '22 at 13:02

score 1 · Accepted Answer · answered Oct 06 '22 at 13:04

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

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

1 Answers1