CODEWITHSUNDEEP
sqlsql-server
Ullas
Ullas

Reputation: 3

Extracting date from within a Varchar

I have the data as below in one of the column. SEMANA 50-15 (13-12-15) Datatype : Varchar

I want to convert above data to this format 2015-12-13 in Microsoft- SQL. Please help me with the syntax.

Upvotes: 0

Views: 97

Answers (4)

d219
d219

Reputation: 2835

If the date is always at the end and is always in 'DD-MM-YY)' format (i.e. there are 8 characters in the date and the date ends one character before the end) then this should do it:

SELECT CONVERT(DATE, REPLACE(SUBSTRING(YourFieldName, LEN(YourFieldName)-8,8),'-','/'), 3) 
FROM YourTableName

To break it down this is assuming that is can find the start of the date eight characters in from the end (so must be in DD-MM-YY format), it extracts the date with the SUBSTRING command. It then does a REPLACE to change '-' to '/' in order that the CONVERT command will work and return a date in the format you have specified.

Note that the above returns a Date, if you specifically need to return a VARCHAR then you can apply a second CONVERT, see https://www.w3schools.com/sql/func_sqlserver_convert.asp or the Microsoft page for more information on the different options for CONVERT if you are interested.

Upvotes: 1

John K. N.
John K. N.

Reputation: 158

You could to the following for a simple UPDATE statement:

UPDATE <TABLE > 
SET    <COLUMN > = LEFT(<COLUMN >, PATINDEX('%(%', <COLUMN >)) -- left part of string 
       + CAST(CONVERT(
           DATE,
           SUBSTRING(
               <COLUMN >,
               PATINDEX('%(%', <COLUMN >) + 1,
               PATINDEX('%)%', <COLUMN >) - 1 - PATINDEX('%(%', <COLUMN >)
           ),
           5
       ) AS VARCHAR(12))-- converted part of string 
 + RIGHT(<COLUMN >, LEN(<COLUMN >) - PATINDEX('%)%', <COLUMN >) + 1) -- right part of string

Please replace <COLUMN> with your column name, and <TABLE> with the name of your table in the above script

I derived the statement from the following testing environment:

DECLARE @vValue1 VARCHAR(30)
DECLARE @iStartVal INT
DECLARE @iEndVal INT
DECLARE @iStringLength INT
DECLARE @iSearchStringLenght INT

SET @vValue1 = 'SEMANA 50-15 (13-12-15)'

SET @iStartVal = PATINDEX('%(%', @vValue1)
SET @iEndVal = PATINDEX('%)%', @vValue1)
SET @iStringLength = @iEndVal -1 - @iStartVal

PRINT @vValue1
PRINT @iStartVal
PRINT @iEndVal 
PRINT @iStringLength

PRINT CONVERT(
                 DATE,
                 SUBSTRING(
                     @vValue1,
                     PATINDEX('%(%', @vValue1) + 1,
                     PATINDEX('%)%', @vValue1) - 1 - PATINDEX('%(%', @vValue1)
                 ),
                 5
             ) 
PRINT LEFT(@vValue1, @iStartVal)
PRINT RIGHT(@vValue1, LEN(@vValue1) - @iEndVal + 1)

PRINT LEFT(@vValue1, PATINDEX('%(%', @vValue1)) -- left part of string 
           + CAST(CONVERT(
               DATE,
               SUBSTRING(
                   @vValue1,
                   PATINDEX('%(%', @vValue1) + 1,
                   PATINDEX('%)%', @vValue1) - 1 - PATINDEX('%(%', @vValue1)
               ),
               5
           ) AS VARCHAR(12)) -- converted part of string 
     + RIGHT(@vValue1, LEN(@vValue1) - PATINDEX('%)%', @vValue1) + 1)

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

If the date is always in parenthesis and there is only one pair of parenthesis and the format is dd-mm-yy (Italian) the following could help.

It uses charindex() to locate the positions of the parenthesis and extracts the substring between them using substring(). That substring is then converted to a date and converted back to a varchar with the desired format.

SELECT convert(varchar,
               convert(date,
                       substring(<column>,
                                 charindex('(',
                                           <column>)
                                 +
                                 1,
                                 charindex(')',
                                           <column>)
                                 -
                                 charindex('(',
                                           <column>)
                                 -
                                 1),
                       5),
               20)
       FROM <table>;

Replace <table> and <column> with your column and table name.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

Removing all test until the first bracket and replacing second bracket, casting the date with the conversion format dd/mm/yy and casting back to char(10) - this does require all rows to have valid dates in the brackets.

SELECT
  convert(char(10),
    convert(date,REPLACE(STUFF(col,1,charindex('(',col),''), ')', ''), 3), 126)
FROM (values('SEMANA 50-15 (13-12-15)'), ('TEST (18-1-18)')) x(col)

Upvotes: 1

Related Questions