How to extract values from column and update result in another column

Question

i have this table:

CREATE TABLE MyTable (
    IdDate int,
    FullDate varchar(255)
);

insert into MyTable (IdDate,FullDate)
VALUES (0, 'Nº1 (26) - Friday 4, January 2014'),
       (0,'Nº2 (64) - Monday 10, February 2015')

I wanna extract from FullDate something like this:

1 2014 01 04
2 2015 02 10

1st number is extracted from Nº1
2nd number is extracted from Year
3rd number is extracted from Month (convert January to 01)
4th number is extracted from day (if day < 10, add 0 at the beginning: 01,02... )

And update the new value extracted in first column called IdDate

My final result should be like:

IdDate        FullDate
120140104     Nº1 (26) - Friday 4, January 2014
220150210     Nº2 (64) - Monday 10, February 2015

The root of your problem is that you are storing dates as strings. Datatypes exists for a reason, don't do this to yourself. https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type — Sean Lange, Aug 30 '18 at 15:16
Yes i know. But i dont have another choice. When I get the new IdDate, I will remove the column FullDate — Raul Escalona, Aug 30 '18 at 15:17
Can you share some actual sample data? Meaning a create table script followed by a couple of insert statements. Oh goodness....I think your column actually has all that extra garbage in there??? — Sean Lange, Aug 30 '18 at 15:19
Do you really have misspelled months in your data? Or is that just a typo while posting? — Sean Lange, Aug 30 '18 at 15:29

John Cappelletti · Accepted Answer · 2018-08-30T16:12:30.467

If open to a helper Table-Valued Function:

Example

Declare @YourTable table (IdDate int,FullDate varchar(max))
Insert Into @YourTable values
 (0,'Nº1 (26) - Friday 4, January 2014')
,(0,'Nº2 (64) - Monday 10, February 2015')

Update A
   set IdDate = substring(Pos1,3,10)
              + try_convert(varchar(10),try_convert(date,Pos6+' '+Pos5+' '+Pos7),112)
 From  @YourTable A
 Cross Apply [dbo].[tvf-Str-Parse-Row](FullDate,' ') B

Returns

IDDate      FullDate
120140104   Nº1 (26) - Friday 4, January 2014
220150210   Nº2 (64) - Monday 10, February 2015

If it Helps with the Visualization, the TVF Returns

The Function if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
)

Or Without the Function

Update A
   set IdDate = substring(Pos1,3,10)
              + try_convert(varchar(10),try_convert(date,Pos6+' '+Pos5+' '+Pos7),112)
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(FullDate,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

EDIT

This is an expanded version of Shawn's cleaner solution

Update @YourTable 
   set IdDate = substring(left(FullDate,charindex(' ',FullDate)-1),3,25)
               +try_convert(varchar(10),try_convert(date,replace(substring(FullDate, charindex(',', FullDate) - 2, 100), ',', '')),112)


Select * from @YourTable

Thanks for reply, but when i run your code show me IdDate = Null. Isnt like your screenshot — Raul Escalona, Aug 30 '18 at 15:59
@RaulEscalona try_convert() will return a NULL if the conversion fails. Misspelled date? Extra spaces? Bogus Date like June 31 ? — John Cappelletti, Aug 30 '18 at 16:01
@RaulEscalona here is a dbFiddle https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b23683e56877631837debfd81bbf2442 — John Cappelletti, Aug 30 '18 at 16:08
It works correctly in your link, but in my database it does not. I am using SQL SERVER Express 2017 — Raul Escalona, Aug 30 '18 at 16:11
@RaulEscalona See the EDIT in my answer. I just expanded Shawn's solution — John Cappelletti, Aug 30 '18 at 16:13

shawnt00 · Answer 2 · 2018-08-30T15:53:17.557

This will extract a date value. It just looks for the comma, backs up a few characters and grabs the date, strips the comma and treats it as a military date.

select convert(date,  
   replace(substring(FullDate, charindex(',', FullDate) - 2, 100), ',', ''), 106)

Use format() or date style 112 to get the output the way you need. The first character is apparently just substring(FullDate, 3, 1) so just append that to the front.

score 1 · Answer 3 · answered Aug 30 '18 at 15:44

If you have to wrestle this without using a function like John posted (which is what I would do) you can dig in and start a nightmarish path of string functions. The challenge is that t-sql is not great at string manipulation.

This seems to work on the sample data you provided. Note that if you have data that can't be converted to a date this will fail.

update MyTable
set IdDate = substring(FullDate, 3, CHARINDEX(' ', FullDate) - 3)
    + Right(FullDate, 4)
    + right('0' + convert(varchar(2), datepart(month, convert(date, replace(substring(substring(FullDate, charindex('-', FullDate) + 2, len(FullDate)), charindex(' ', substring(FullDate, charindex('-', FullDate) + 2, len(FullDate))) + 1, len(FullDate)), ',', '')))), 2)
    + right('0' + convert(varchar(2), datepart(day, convert(date, replace(substring(substring(FullDate, charindex('-', FullDate) + 2, len(FullDate)), charindex(' ', substring(FullDate, charindex('-', FullDate) + 2, len(FullDate))) + 1, len(FullDate)), ',', '')))), 2)

select * from MyTable

Then go ask the person who decided to store data like this why they did it? This is not how to handle data at all.

Error converting a string of characters in the date and / hour. my column FullDate is varchar(255) — Raul Escalona, Aug 30 '18 at 16:01
You must have some garbage data. It works fine using your sample data and table. — Sean Lange, Aug 30 '18 at 16:02
When I get the new IdDate, I will remove the column FullDate — Raul Escalona, Aug 30 '18 at 16:03
Oy!!! You are going to go from one terrible design to another. Stop the insanity!!! This is two pieces of information. A number and a date. Cramming them together is still violating 1NF. Make this into two columns or you will continue to have problems. — Sean Lange, Aug 30 '18 at 16:04

Tab Alleman · Answer 4 · 2018-08-30T15:35:41.390

0

1st number is extracted from Nº1
2nd number is extracted from Year
3rd number is extracted from Month (convert January to 01)
4th number is extracted from day (if day < 10, add 0 at the beginning: 01,02... )

Use SQL Server's string functions to do the following

Get the characters between Nº and the first blank space that follows it.
Get the last 4 characters
Use a CASE expression to generate a month number based on what month name is LIKE the string
Get the 2 characters before the comma, and replace any blank characters in that with a 0.

And concatenate those 4 values together.

edited Aug 30 '18 at 15:35

answered Aug 30 '18 at 15:29

Tab Alleman

31,483
7
36
52

But how can i do that? – Raul Escalona Aug 30 '18 at 15:33
1

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-2017 (also added to answer). You have asked a broad question and I have given you a broad strategy. Use this link to do some research and make an attempt to solve the problem. If you get stuck on any one of these techniques, make that a new question, so that you can focus your question on one specific technique. – Tab Alleman Aug 30 '18 at 15:37
1

I disagree Tab. The OP doesn't know what to do. They have provided functional ddl and sample data. It is clear what they expect as output. This data is such a complete trainwreck that it isn't easy to unravel this mess. – Sean Lange Aug 30 '18 at 15:39

How to extract values from column and update result in another column

4 Answers4

Linked