Home / Questions / 25926
Accepted
nachfren
Asked: 2020-10-04 08:54:21 +0800 CST 2020-10-04 08:54:21 +0800 CST

Extract date within a text string SQL SERVER

  • 772

I have the following string:Un texto cualquiera 22/12/2016 fin texto

I am trying to get the position of 22/12/2016with the PATINDEX function:

SELECT PATINDEX('__/__/____', 'Un texto cualquiera 22/12/2016 fin texto');

But the function returns 0, do you know what I'm doing wrong?

The underscore _according to the microsoft link is for one character. patterns

sql-server

2 Answers

  1. Greetings nachfren , considering that the date format is dd/mm/yyyy , this will be useful:

    declare @cadenaDeTexto varchar(100)
set @cadenaDeTexto = 'Un texto cualquiera 22/12/2016 fin texto'

select substring(@cadenaDeTexto,
            patindex('%[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]%', @cadenaDeTexto),
            10) as "Fecha Extraida"

    Resulting:

    Fecha Extraida
22/12/2016

    The present code will not work if the format changes as d/m/yyyy or dd/m/yyyy or d/mm/yyyy . I hope you made me understand, if not, hit the comments and we'll see how we support you brother :D

    • 6
  2. Best Answer

    I think the problem is that the character is missing%

    SELECT PATINDEX('__/__/____', 'Un texto cualquiera 22/12/2016 fin texto');

    This pattern would only be valid for this string '22/12/2016'because it has no way of identifying multiple characters.

    If we change it to

    SELECT PATINDEX('%__/__/____%', 'Un texto cualquiera 22/12/2016 fin texto');

    It should work now

    • 5