How to take apart information between hyphens in S

Question

How would I take apart a column that contains string:

92873-987dsfkj80-2002-04-11
20392-208kj48384-2008-01-04

Data would look like this:

Filename     Yes/No     Key
Abidabo      Yes        92873-987dsfkj80-2002-04-11
Bibiboo      No         20392-208kj48384-2008-01-04

Want it to look like this:

Filename     Yes/No     Key
Abidabo      Yes        92873-987dsfkj80-20020411
Bibiboo      No         20392-208kj48384-20080104

whereby I would like to concat the dates in the end as 20020411 and 20080104. From the right side, the information is the same always. From the left it is not, otherwise I could have concatenated it. It is not an import issue.

Answer 1

Depending on the SQLSERVER version you are using, you can use STRING_SPLIT which requieres COMPATIBILITY_LEVEL 130. You can also build your own User Defined Function to split the contents of a field and manipulate it as you need, you can find some useful examples of SPLIT functions in this thread:

Split function equivalent in T-SQL?

Answer 2

Assuming I'm correct and the date part is always on the right side of the string, you can simply use RIGHT and CAST to get the date (assuming, again, that the date is represented as yyyy-mm-dd):

SELECT CAST(RIGHT(YourColumn, 10) As Date)
FROM YourTable

However, Panagiotis is correct in his comment - You shouldn't store data like that. Each column in the database should hold only a single point of data, be it string, number or date.

Update following your comment and the updated question:

SELECT LEFT(YourColumn, LEN(YourColumn) - 10) + REPLACE(RIGHT(YourColumn, 10), '-', '')
FROM YourTable

will return the desired results.

Answer 3

As mentioned in the comments already, storing data like this is a bad idea. However, you can obtain the dates from those strings by using a RIGHT function like so:

SELECT RIGHT('20392-208kj48384-2008-01-04', 10) 

Output:

2008-01-04