-1

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.

Probs
  • 343
  • 2
  • 6
  • 20
  • 5
    *Don't* store data like that in the first place. This string requires parsing, not splitting. Use a proper table with three different columns. You could write eg a regex to parse the original data, transform the fields to appropriate types and copy them to a proper table – Panagiotis Kanavos May 30 '17 at 08:52
  • The values look like fixed-width fields that came from some file. You could use any of SQL Server's import mechanisms to load a fixed-width file into a table, eg bcp, BULK INSERT, SSIS. – Panagiotis Kanavos May 30 '17 at 09:02

3 Answers3

0

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • I don't need it as date, but as a string like: 20020411. With the CAST(RIGHT(..)) it is still shown as 2002-04-11 (with hyphens) And I would like it without hyphens – Probs May 30 '17 at 09:08
0

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
SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
0

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?

Sergio Prats
  • 1,043
  • 1
  • 14
  • 19