Bramat - 8 months ago 31

SQL Question

I have a table of data. In that table there's a column called "TypeTag" that can be either an 11 digits number or 11 digits number "_" followed by 3 more digits. Meaning: 'XXXXXXXXXXX' OR 'XXXXXXXXXXX_XXX'.

I need the first part only - so I wrote a query:

`SELECT`

(CASE

WHEN TypeTag LIKE '%_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4)

ELSE TypeTag

END)

FROM `DailyReport`

WHERE DATE>='2016-07-01'

The problem is that ALL of the values return without their last 4 characters, the case seems not to work (or maybe the LIKE doesn't work) because all of the records go into the first "WHEN" and not to "ELSE".

For example, if my original data set is:

`56329856721`

56329856722_502

56329856723

56329856724

56329856725_633

56329856726

Then the response I get is:

`5632985`

56329856722

5632985

5632985

56329856725

5632985

I'm using MySQL (SQLyog specifically). Does anyone know what's wrong with my query? or how to write the condition in a way that I'll get the needed result?

Thank you all!!

Answer

This is your `CASE`

statement:

```
(CASE WHEN TypeTag LIKE '%_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4)
ELSE TypeTag
END)
```

You have forgotten that `_`

is a wildcard (that matches exactly one character). So, the `LIKE`

is checking that the value has at least one character.

You can escape it:

```
(CASE WHEN TypeTag LIKE '%\_%' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4)
ELSE TypeTag
END)
```

Or, if you don't like `\`

as the escape character, you can define your own:

```
(CASE WHEN TypeTag LIKE '%!_%' ESCAPE '!' THEN LEFT(TypeTag, LENGTH(TypeTag) - 4)
ELSE TypeTag
END)
```

Or, you can simplify the entire logic by using `SUBSTRING_INDEX()`

:

```
SUBSTRING_INDEX(TypeTag, '_', 1)
```

That would be my preferred solution.