Problem with Concat Function

Databases
1

Hi there.

I can not working this CONCAT Function:


CONCAT
   (
      SUBSTRING_INDEX(`ab00-1-380216`,'-',1)
    , SUBSTRING_INDEX(REVERSE(`ab00-1-380216`),'-',1)
   )

The input: ab00-1-380216
The output correct: ab00138216

I have this output incorrect: ab00612083

Can u help me?

2 

CONCAT
   (
      SUBSTRING_INDEX(`ab00-1-380216`,'-',1)
    , SUBSTRING_INDEX(`ab00-1-380216`,'-'[B][COLOR="Red"],[SIZE="3"]-1[/SIZE][/COLOR][/B])
   )

MySQL :: MySQL 5.1 Reference Manual :: 11.5 String Functions

3

Thanks Sir.

Your output is: ab00380216.

I need this output: ab00138216

Add 1 value before 4 caracters of the string, delete 0 value in the seven position of the string… it’s possible?

4

So what you want is the string without the two ‘-’ ?
Try replace

5

Thanks Sir.

In the u suggestion already deleted ‘-’ in the string.

The input: ab00-1-380216
Your output: ab00 380 216

My output: ab00 (Add 1) 38 (Delete 0) 216
ab00 1 38 216

6

You’re welcome. Forget about SUBSTRING_INDEX. Use replace on the original string, replacing ‘-’ with ‘’.

7

Thanks Sir.

I try this:

SELECT
REPLACE(CONCAT
   (
      SUBSTRING_INDEX('ab00-1-380216','-',1)
    , SUBSTRING_INDEX('ab00-1-380216','-',-1) 
   ), '-', '') A

The output: ab00380216

Now I need this output: ab00138216

8

I try this:

SELECT
REPLACE(CONCAT
   (
      SUBSTRING_INDEX('ab00-1-380216','-',2)
    , SUBSTRING_INDEX('ab00-1-380216','-',-1) 
   ), '-', '') A;

The output: ab001380216

Now I need this output: ab00138216

I need delete this 0 value… ab00 138 [0] 216

9

Do NOT use SUBSTRING_INDEX!
Don’t use REPLACE with SUBSTRING_INDEX.
Use only REPLACE.

Ahh, I didn’t see that ‘0’ that has to go… hmm, is it always in the same position?

10

Thanks Sir.