MYSQL concat request and string substraction

Question

I have a mysql table below:

id   user_id   ticket 
1    39        56478939
2    51        42234951
3    40        56478940
4    52        67284952

Where ticket (for example for first row) = 564789 + 39 (user_id) My request looks like:

SELECT CONCAT_WS(";", id, user_id, ticket) FROM `signals`

And I will get:

1;39;56478939
2;51;42234951
...

How can I get this type of data using MYSQL request (remove from ticket user_id at the end of ticket):

1;39;564789
2;51;422349
...

The request should be fast enough: Thanks you very much!

AnandPhadke · Accepted Answer · 2012-10-09 09:57:50Z

1

try this:

SELECT CONCAT_WS(";", id, user_id, LEFT(ticket,length(ticket)-length(user_id))) from table1;

FIDDLE DEMO

edited Oct 9, 2012 at 9:57

answered Oct 9, 2012 at 9:52

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XTRUST.ORG Over a year ago

We don't know ticket length or length of user_id

venkatKA · Accepted Answer · 2012-10-09 09:47:20Z

0

Try this :

           SELECT CONCAT_WS(";", id, user_id, LEFT(ticket,6)) FROM `signals`

answered Oct 9, 2012 at 9:47

venkatKA

2,3991 gold badge20 silver badges22 bronze badges

1 Comment

venkatKA Over a year ago

@user889349 I've assumed your ticket length would be 6. Consider Joe's answer if its dynamic.

Joe G Joseph · Accepted Answer · 2012-10-09 09:56:55Z

0

try this:

SELECT CONCAT_WS(";", id, user_id, left(ticket,length(ticket)-length(user_id)))
FROM `signals`

edited Oct 9, 2012 at 9:56

answered Oct 9, 2012 at 9:51

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Comments

Devart · Accepted Answer · 2012-10-09 10:00:26Z

0

You can use this query -

SELECT (ticket - user_id) DIV
  CASE
    WHEN user_id < 10 THEN 10
    WHEN user_id < 100 THEN 100
    ELSE 1000 END
FROM signals;

Add more WHEN cases if you need.

Then apply it to your query.

answered Oct 9, 2012 at 10:00

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

MYSQL concat request and string substraction

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related