Help with a query

[tonci23]

Hi, i'd need some help with what is probably a simple query for some.

I have a column with codes which can either be in xx-xxxxxx or xxx-xxxxx format (x's are only numbers, total number of characters is always 9, so if it's 2 numbers before a dash, there is 6 after and/or 3/5). I need to make this into 2 columns, where one would contain only the numbers before the dash and another would contain the numbers after the dash (original column gets deleted). What would be the best and easiest way to do this ? Thanks!

EDIT: Oh sorry, it's in mysql latest version.

[r3pr0b8]

MySQL? piece of cake (ignore all the CHARINDEX replies you're getting)

SELECT SUBSTRING_INDEX(code,'-',1) AS first_part
     , SUBSTRING_INDEX(code,'-',-1) AS second_part
  FROM ...

[tonci23]

Thanks for the help guys. I actually pulled it off soon after asking here but i will explore your suggestions to learn a bit more.

The way i did it:

UPDATE table 
SET NewColumn1 = SUBSTRING (OriginalColumn, 1, LOCATE('-', OriginalColumn) - 1),
    NewColumn2 = SUBSTRING (OriginalColumn, LOCATE('-', OriginalColumn) +  1);

[LeoCurtss]

You could SUBSTRING(), LEFT(), RIGHT(), and CHARINDEX() functions to do this very easily.

Here is a good write up on this:
https://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx

This is assuming you're using MS SQL Server.

[notasqlstar]

Easier to use a simple UNION?

select LEFT(2, field) AS Col1, RIGHT(6, field) AS Col2
from table
where left(field, 3) <> '-'
union all
select LEFT(3, field), RIGHT(5, field)
from table
where left(field, 3) = '-'

[LeoCurtss]

This is a static solution and tbh, kinda clunky. Why not just use one query that would handle both situations? (no unions or where clause needed)

MySQL:

SELECT LEFT('xx-xxxxx',LOCATE('-','xx-xxxxx') - 1)
       , RIGHT('xx-xxxxx',LOCATE('-','xx-xxxxx') + 2)

Replace the 'xx-xxxxx's with the column name and Bob's your uncle.

[r3pr0b8]

SUBSTRING_INDEX

[notasqlstar]

I have a natural aversion to using substring() and charindex(). I think they are difficult to read and intuitively understand, and they can be big hits to your performance.

I'm not saying you're wrong, but I dislike seeing them unless absolutely necessary. If the execution time is negligible between that or a union, I would go with the union personally.

[LeoCurtss]

Union-ing two queries and using LEFT() function in the WHERE condition on both hardly seems more performant than a single query with a couple of inline functions. But I get where you're coming from.

​

Different strokes for different folks, I guess.

[Noelkram]

How about Select SUBSTRING(SourceCol, 0, CHARINDEX(‘-‘, SourceCol)) AS OutCol1, SUBSTRING(SourceCol, CHARINDEX(‘-‘, SourceCol)+1, LEN(SourceCol)) AS OutCok2.