Manipulate strings on MySQL database

Question

I have these strings : X322-1-381128 and X322-1-383128

I need manipulate these strings for get output :

X322138128 and X322138328

I have tried withous success this solution, because the output is only : X322138128

Please can you help me ?

mysql> SELECT
    'X322-1-381128' string,
    SUBSTRING_INDEX('X322-1-381128', '-', 1) a,
    SUBSTRING('X322-1-381128', 6, 1) b,
    SUBSTRING('X322-1-381128', 8, 2) c,
    SUBSTRING('X322-1-381128' ,- 3) d,
    CONCAT(
        SUBSTRING_INDEX('X322-1-381128', '-', 1),
        SUBSTRING('X322-1-381128', 6, 1),
        SUBSTRING('X322-1-381128', 8, 2),
        SUBSTRING('X322-1-381128' ,- 3)
    ) output,
    'X322-1-383128' string,
    SUBSTRING_INDEX('X322-1-381128', '-', 1) a,
    SUBSTRING('X322-1-381128', 6, 1) b,
    SUBSTRING('X322-1-381128', 8, 2) c,
    SUBSTRING('X322-1-381128' ,- 3) d,
    CONCAT(
        SUBSTRING_INDEX('X322-1-383128', '-', 1),
        SUBSTRING('X322-1-383128', 6, 1),
        SUBSTRING('X322-1-383128', 8, 2),
        SUBSTRING('X322-1-383128' ,- 3)
    ) output;
+---------------+------+---+----+-----+------------+---------------+------+---+----+-----+------------+
| string        | a    | b | c  | d   | output     | string        | a    | b | c  | d   | output     |
+---------------+------+---+----+-----+------------+---------------+------+---+----+-----+------------+
| X322-1-381128 | X322 | 1 | 38 | 128 | X322138128 | X322-1-383128 | X322 | 1 | 38 | 128 | X322138128 |
+---------------+------+---+----+-----+------------+---------------+------+---+----+-----+------------+
1 row in set

score 1 · Answer 1 · answered Nov 19 '19 at 09:05

1

use replace('X322-1-381128','-')

answered Nov 19 '19 at 09:05

ALEX WANG

11
1

select replace('X322-1-381128','-','') – ALEX WANG Nov 19 '19 at 09:07
Thanks for reply, but the output must contain 10 characters, instead the input is for 11 characters, only the replace is not sufficient – Nov 19 '19 at 09:20

Sebastian Brosch · Accepted Answer · 2019-11-19T09:17:20.437

Looks like your last SUBSTRING is wrong. You want to remove all - and the third last number:

SELECT
  'X322-1-381128' string,
  SUBSTRING_INDEX('X322-1-381128', '-', 1) a,
  SUBSTRING('X322-1-381128', 6, 1) b,
  SUBSTRING('X322-1-381128', 8, 2) c,
  CONCAT(SUBSTRING('X322-1-381128' ,-4,1), SUBSTRING('X322-1-381128' ,-2)) d,
  CONCAT(
    SUBSTRING_INDEX('X322-1-381128', '-', 1),
    SUBSTRING('X322-1-381128', 6, 1),
    SUBSTRING('X322-1-381128', 8, 2),
    SUBSTRING('X322-1-381128' ,- 3)
  ) output,

  'X322-1-383128' string,
  SUBSTRING_INDEX('X322-1-383128', '-', 1) a,
  SUBSTRING('X322-1-383128', 6, 1) b,
  SUBSTRING('X322-1-383128', 8, 2) c,
  CONCAT(SUBSTRING('X322-1-383128' ,-4,1), SUBSTRING('X322-1-383128' ,-2)) d,
  CONCAT(
    SUBSTRING_INDEX('X322-1-383128', '-', 1),
    SUBSTRING('X322-1-383128', 6, 1),
    SUBSTRING('X322-1-383128', 8, 2),
    SUBSTRING('X322-1-383128' ,-4,1),
    SUBSTRING('X322-1-383128' ,-2)
  ) output;

Since your string manipulation is very static you can also use the following much simpler solution:

SELECT 'X322-1-381128' string,
  CONCAT(LEFT(REPLACE('X322-1-381128', '-', ''), 8), RIGHT(REPLACE('X322-1-381128', '-', ''), 2))
UNION ALL
SELECT 'X322-1-383128' string,
  CONCAT(LEFT(REPLACE('X322-1-383128', '-', ''), 8), RIGHT(REPLACE('X322-1-383128', '-', ''), 2))

demo on dbfiddle.uk

Manipulate strings on MySQL database

2 Answers2