Extract date within a SQL SERVER text string


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

I'm trying to get the position of 22/12/2016 with 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 low bar _ according to the microsoft link is for a character. Patterns

asked by nachfren 03.10.2016 в 18:54

2 answers


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 to identify several characters.

If we change it to

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

It should already work

answered by 03.10.2016 / 20:16

Greetings nachfren , considering that the format of the dates is dd / mm / yyyy

strong>, this will be useful to you:

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 in:

Fecha Extraida

This code will not work if the format changes to d / m / yyyy or dd / m / yyyy or d / mm / yyyy I hope you let me understand, otherwise, give comments and see how we support you brother: D

answered by 03.10.2016 в 19:41