Curiosidad tabla y vista que filtra datos númericos sobre columna varchar

Prefiero validar si un varchar es numerico usando la función ISNUMERIC en vez
de usar una expresión regular.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/profile/gux
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Salvador Ramos" wrote:

Hola a todos,

El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
campo varchar introduzco datos numéricos y no numéricos, hago una vista para
filtrar sólo los numéricos. Si luego hago una select sobre la vista
funciona, pero si esa select lleva una condición where da error. Aquí tenéis
un script que reproduce dicha curiosidad:
(Por supuesto no vamos a entrar en hacerlo de otra forma con los números en
columnas numéricas, simplemente estudiar este caso)

use Pruebas
GO

drop table mezcla
GO

create table mezcla(id int identity(1, 1), pupurri varchar(20))
GO

insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO

create view solonum as
select id, cast(pupurri as int) num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

select *
from solonum

select *
from solonum
where num = 15

El mensaje es el siguiente:
Mens. 245, Nivel 16, Estado 1, Línea 1
Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
int.

El problema es que esto ya lo tienen implementado sobre 2000 (que funciona
correctamente) y ahora para migrar a 2005 se encuentran con el error.
Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
modificaciones en el diseño ?

Muchas gracias

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm

Aclaración: Mi comentario iba dirigido a cómo filtrar números... no es una
solución al comportamiento que dice Salvador.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/profile/gux
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Gux (MVP)" wrote:

Prefiero validar si un varchar es numerico usando la función ISNUMERIC en vez
de usar una expresión regular.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/profile/gux
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Salvador Ramos" wrote:

> Hola a todos,
>
> El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
> campo varchar introduzco datos numéricos y no numéricos, hago una vista para
> filtrar sólo los numéricos. Si luego hago una select sobre la vista
> funciona, pero si esa select lleva una condición where da error. Aquí tenéis
> un script que reproduce dicha curiosidad:
> (Por supuesto no vamos a entrar en hacerlo de otra forma con los números en
> columnas numéricas, simplemente estudiar este caso)
>
> use Pruebas
> GO
>
> drop table mezcla
> GO
>
> create table mezcla(id int identity(1, 1), pupurri varchar(20))
> GO
>
> insert into mezcla values('pepe')
> insert into mezcla values('001')
> insert into mezcla values('15')
> insert into mezcla values('juan')
> insert into mezcla values('1')
> insert into mezcla values('150')
> GO
>
> create view solonum as
> select id, cast(pupurri as int) num
> from mezcla
> where pupurri not like '%[A-Z]%'
> and pupurri like '%[0-9]%'
> GO
>
> select *
> from solonum
>
> select *
> from solonum
> where num = 15
>
> El mensaje es el siguiente:
> Mens. 245, Nivel 16, Estado 1, Línea 1
> Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
> int.
>
> El problema es que esto ya lo tienen implementado sobre 2000 (que funciona
> correctamente) y ahora para migrar a 2005 se encuentran con el error.
> Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
> modificaciones en el diseño ?
>
> Muchas gracias
>
> Un saludo
> Salvador Ramos
>
> www.helpdna.net (información sobre SQL Server y Microsoft .Net)
> www.helpdna.net/acerca_de_salvador_ramos.htm
>
>
>
>

Totalmente de acuerdo :-)

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm


"Gux (MVP)" escribió en el mensaje
news:

Prefiero validar si un varchar es numerico usando la función ISNUMERIC en
vez
de usar una expresión regular.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/profile/gux
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Salvador Ramos" wrote:

Hola a todos,

El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
campo varchar introduzco datos numéricos y no numéricos, hago una vista
para
filtrar sólo los numéricos. Si luego hago una select sobre la vista
funciona, pero si esa select lleva una condición where da error. Aquí
tenéis
un script que reproduce dicha curiosidad:
(Por supuesto no vamos a entrar en hacerlo de otra forma con los números
en
columnas numéricas, simplemente estudiar este caso)

use Pruebas
GO

drop table mezcla
GO

create table mezcla(id int identity(1, 1), pupurri varchar(20))
GO

insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO

sean
create view solonum as
select id, cast(pupurri as int) num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

select *
from solonum

select *
from solonum
where num = 15

El mensaje es el siguiente:
Mens. 245, Nivel 16, Estado 1, Línea 1
Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
int.

El problema es que esto ya lo tienen implementado sobre 2000 (que
funciona
correctamente) y ahora para migrar a 2005 se encuentran con el error.
Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
modificaciones en el diseño ?

Muchas gracias

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm

Hola Salva,

1 - Recuerda que SS usa la definicion de la vista, ya que esta no esta
materializada, por lo que la sentencia final sera:

select id, cast(pupurri as int) num
from mezcla
where
pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
and pupurri = 15

Este es el plan estimado. fijate que hace el CAST para todas las filas.

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
AND [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))

Como vez, hace la conversion a entero para todas las filas. El problema es
que usas la columna [num] en la expresion de la clausula "where", y la
comparas contra un entero. Como la vista no esta materializada, SS usa el
plan anterior.

Yo te recomiendo esto:

1 - Puedes crear la vista para filtrar, pero sin convertir / cast.
2 - Filtrar en la sentencia "select" usando una cadena, en vez de un numero
3 - Hacer el cast en el resultado final.

Por cierto, esta expresion filtra las columnas que contienen solo #s (fijate
en la clausula "where").

create view solonum
as
select id, pupurri
from mezcla
WHERE pupurri NOT LIKE '%[^0-9]%'
GO

select id, CAST(pupurri AS int) num
from solonum
WHERE pupurri = '15'
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[^0-9]%'))

Tambien puedes dejar la vista como esta, pero adiciona la columna [pupurri]
y usa esta en la expresion que usas en la clausula "where" de la sentencia
"select" que referencia la vista. De esta forma SS solo necesita convertir el
resultado final.

create view solonum
as
select id, pupurri, CAST(pupurri AS int) AS num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

SET SHOWPLAN_TEXT ON
GO

select id, pupurri, num
from solonum
WHERE pupurri = '15'
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND
[Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))

Usar solamente la funcion ISNUMERIC no es de ayuda aqui, pues ocurrira lo
mismo que en el script original.

create view solonum
as
select id, CAST(pupurri AS int) AS num
from mezcla
WHERE ISNUMERIC(pupurri) = 1
GO

SET SHOWPLAN_TEXT ON
GO

select id, num
from solonum
WHERE num = 15
GO

SET SHOWPLAN_TEXT OFF
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0) AND isnumeric([Northwind].[dbo].[mezcla].[pupurri])=(1)))


AMB


"Salvador Ramos" wrote:

Hola a todos,

El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
campo varchar introduzco datos numéricos y no numéricos, hago una vista para
filtrar sólo los numéricos. Si luego hago una select sobre la vista
funciona, pero si esa select lleva una condición where da error. Aquí tenéis
un script que reproduce dicha curiosidad:
(Por supuesto no vamos a entrar en hacerlo de otra forma con los números en
columnas numéricas, simplemente estudiar este caso)

use Pruebas
GO

drop table mezcla
GO

create table mezcla(id int identity(1, 1), pupurri varchar(20))
GO

insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO

create view solonum as
select id, cast(pupurri as int) num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

select *
from solonum

select *
from solonum
where num = 15

El mensaje es el siguiente:
Mens. 245, Nivel 16, Estado 1, Línea 1
Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
int.

El problema es que esto ya lo tienen implementado sobre 2000 (que funciona
correctamente) y ahora para migrar a 2005 se encuentran con el error.
Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
modificaciones en el diseño ?

Muchas gracias

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm

Muchas gracias Alejandro por esta respuesta tan clara y elaborada.

Como bien dices una alternativa sería crear la vista sin convertir, select
usando cadena, y conversión en el resultado final.

Ya por curiosidad he intentado materializar la vista (incluso con un indice
clustered por ambas columnas de la tabla, no iba buscando la mejor solución,
sino una alternativa que no diese error a partir de una vista
materializada), y me sigue dando error, sigue leyendo todas las filas de la
tabla. Te paso el script que he probado y que tambien da error de
conversion, cuando entiendo que deberia funcionar

use Pruebas
GO
drop index i_solonum on solonum
GO
drop view solonum
GO
drop table mezcla
GO
create table mezcla(id int identity(1, 1), pupurri varchar(20)
CONSTRAINT [PK_mezcla_id] PRIMARY KEY CLUSTERED )
GO
insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO
create view dbo.solonum with schemabinding as
select id, cast(pupurri as int) num
from dbo.mezcla
where isnumeric(pupurri) = 1
GO
create unique clustered index i_solonum on solonum(id, num)
GO
select *
from solonum
select *
from solonum
where num = 15

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm


"Alejandro Mesa" escribió en el
mensaje news:

Hola Salva,

1 - Recuerda que SS usa la definicion de la vista, ya que esta no esta
materializada, por lo que la sentencia final sera:

select id, cast(pupurri as int) num
from mezcla
where
pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
and pupurri = 15

Este es el plan estimado. fijate que hace el CAST para todas las filas.

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
AND [Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))

Como vez, hace la conversion a entero para todas las filas. El problema es
que usas la columna [num] en la expresion de la clausula "where", y la
comparas contra un entero. Como la vista no esta materializada, SS usa el
plan anterior.

Yo te recomiendo esto:

1 - Puedes crear la vista para filtrar, pero sin convertir / cast.
2 - Filtrar en la sentencia "select" usando una cadena, en vez de un
numero
3 - Hacer el cast en el resultado final.

Por cierto, esta expresion filtra las columnas que contienen solo #s
(fijate
en la clausula "where").

create view solonum
as
select id, pupurri
from mezcla
WHERE pupurri NOT LIKE '%[^0-9]%'
GO

select id, CAST(pupurri AS int) num
from solonum
WHERE pupurri = '15'
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[^0-9]%'))

Tambien puedes dejar la vista como esta, pero adiciona la columna
[pupurri]
y usa esta en la expresion que usas en la clausula "where" de la sentencia
"select" que referencia la vista. De esta forma SS solo necesita convertir
el
resultado final.

create view solonum
as
select id, pupurri, CAST(pupurri AS int) AS num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

SET SHOWPLAN_TEXT ON
GO

select id, pupurri, num
from solonum
WHERE pupurri = '15'
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:([Northwind].[dbo].[mezcla].[pupurri]=[@1] AND
[Northwind].[dbo].[mezcla].[pupurri] like '%[0-9]%' AND NOT
[Northwind].[dbo].[mezcla].[pupurri] like '%[A-Z]%'))

Usar solamente la funcion ISNUMERIC no es de ayuda aqui, pues ocurrira lo
mismo que en el script original.

create view solonum
as
select id, CAST(pupurri AS int) AS num
from mezcla
WHERE ISNUMERIC(pupurri) = 1
GO

SET SHOWPLAN_TEXT ON
GO

select id, num
from solonum
WHERE num = 15
GO

SET SHOWPLAN_TEXT OFF
GO

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)))
|--Table Scan(OBJECT:([Northwind].[dbo].[mezcla]),
WHERE:(CONVERT(int,[Northwind].[dbo].[mezcla].[pupurri],0)=CONVERT_IMPLICIT(int,[@1],0)
AND isnumeric([Northwind].[dbo].[mezcla].[pupurri])=(1)))


AMB


"Salvador Ramos" wrote:

Hola a todos,

El otro día me plantearon la siguiente curiosidad. Hago una tabla y en un
campo varchar introduzco datos numéricos y no numéricos, hago una vista
para
filtrar sólo los numéricos. Si luego hago una select sobre la vista
funciona, pero si esa select lleva una condición where da error. Aquí
tenéis
un script que reproduce dicha curiosidad:
(Por supuesto no vamos a entrar en hacerlo de otra forma con los números
en
columnas numéricas, simplemente estudiar este caso)

use Pruebas
GO

drop table mezcla
GO

create table mezcla(id int identity(1, 1), pupurri varchar(20))
GO

insert into mezcla values('pepe')
insert into mezcla values('001')
insert into mezcla values('15')
insert into mezcla values('juan')
insert into mezcla values('1')
insert into mezcla values('150')
GO

sean
create view solonum as
select id, cast(pupurri as int) num
from mezcla
where pupurri not like '%[A-Z]%'
and pupurri like '%[0-9]%'
GO

select *
from solonum

select *
from solonum
where num = 15

El mensaje es el siguiente:
Mens. 245, Nivel 16, Estado 1, Línea 1
Error de conversión al convertir el valor varchar 'pepe' al tipo de datos
int.

El problema es que esto ya lo tienen implementado sobre 2000 (que
funciona
correctamente) y ahora para migrar a 2005 se encuentran con el error.
Qué soluciones se os ocurren, a parte de crear nuevas columnas u otras
modificaciones en el diseño ?

Muchas gracias

Un saludo
Salvador Ramos

www.helpdna.net (información sobre SQL Server y Microsoft .Net)
www.helpdna.net/acerca_de_salvador_ramos.htm