June 8, 2009 at 9:12 am
it's simple...i want to create a rule that allows only the characters 0-9, and +-.
i started with a very basic rule, and it did what i wanted, but still allowed the characters i didn't exclude yet.
in the end, i'll make a custom data type that is a varchar with a rule applied against it.
when i added the other characters, it's not doing what I expected., someone tell me what is wrong with my LIKE statement for my rule.
USE [SandBox]
GO
--create a basic rule, no chars.
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[A-Z,a-z]%'
GO
--create a "type" , and bind the rule to teh type
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--a simple test table.
create table example(exampleid int identity,test numchar)
--insert some test data.
insert into example(test) values ('0000')
insert into example(test) values ('00a00') --fails! all is good
insert into example(test) values ('0000&444') --grr not what i wanted, no chars, no spaces,no tab/CrLf, nothing other than 0-9, and -
GO
--drop everything and start fresh
drop table example
drop type numchar
drop rule [NumericCharsOnly]
GO
CREATE RULE [dbo].[NumericCharsOnly]
AS
--new not like string has all the chars to exclude.
@value NOT LIKE '%[A-Z,a-z!@#$%^&*()_=[]{}\ |;:''"<>?/~`]%' --allow 0-9,+ - only?
GO
--create the type and bind it again
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--the test table one more time
create table example(exampleid int identity,test numchar)
insert into example(test) values ('0000')
insert into example(test) values ('00a00') --what? now this does not raise an error
insert into example(test) values ('0000&444') --this does not raise an error either!
I also tried the invers of the rule like this, but it doesn't exclude bad data either:
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value LIKE '%[^A-Z,a-z,-+]%'
Lowell
June 8, 2009 at 9:41 am
ok, it looks like two wrongs make a right...NOT combined with [^0-9] ALMOST looks like it works:
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[^0-9,-+]%'
but the - and + , when added to a numberic string raise an error...
Lowell
June 8, 2009 at 10:14 am
Lowell (6/8/2009)
ok, it looks like two wrongs make a right...NOT combined with [^0-9] ALMOST looks like it works:CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[^0-9,-+]%'
but the - and + , when added to a numberic string raise an error...
First, this makes since as you are lookig for at least one character that isn't 0-9 or - or +. What exactly is the error you get when you add the + or - in the string, and where are you adding these characters?
June 8, 2009 at 10:18 am
the exact error i got was this:
Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'SandBox', table 'dbo.example', column 'test'.
The statement has been terminated.
that is the same error i get when inserting anything alphanumeric, which is expected...but I thought the like condition would exclude the plus and minus.
here's the exact code i tried, slightly tweaked from the original example:
USE [SandBox]
GO
--create a basic rule, no chars.
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[^0-9,-+]%'
GO
--create a "type" , and bind the rule to teh type
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--a simple test table.
create table example(exampleid int identity,test numchar)
--insert some test data.
insert into example(test) values ('0000')
insert into example(test) values ('00a00') --fails! all is good
insert into example(test) values ('0000&444') --fails as expected
insert into example(test) values ('-0000') --failed when i did not want it too
Lowell
June 8, 2009 at 10:34 am
I think that you will need to escape the second "-" in the pattern.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 10:39 am
Lowell (6/8/2009)
the exact error i got was this:Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'SandBox', table 'dbo.example', column 'test'.
The statement has been terminated.
that is the same error i get when inserting anything alphanumeric, which is expected...but I thought the like condition would exclude the plus and minus.
here's the exact code i tried, slightly tweaked from the original example:
USE [SandBox]
GO
--create a basic rule, no chars.
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[^0-9,-+]%'
GO
--create a "type" , and bind the rule to teh type
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--a simple test table.
create table example(exampleid int identity,test numchar)
--insert some test data.
insert into example(test) values ('0000')
insert into example(test) values ('00a00') --fails! all is good
insert into example(test) values ('0000&444') --fails as expected
insert into example(test) values ('-0000') --failed when i did not want it too
Give this a try:
USE [SandBox]
GO
--create a basic rule, no chars.
CREATE RULE [dbo].[NumericCharsOnly]
AS
@value NOT LIKE '%[^0-9,^!-,^+]%' ESCAPE '!'
GO
--create a "type" , and bind the rule to teh type
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--a simple test table.
create table dbo.example(exampleid int identity,test numchar)
--insert some test data.
insert into dbo.example(test) values ('0000')
insert into dbo.example(test) values ('00a00') --fails! all is good
insert into dbo.example(test) values ('0000&444') --fails as expected
insert into dbo.example(test) values ('-0000') --failed when i did not want it too
insert into dbo.example(test) values ('+0000') --failed when i did not want it too
select * from dbo.example
June 8, 2009 at 11:22 am
Perhaps I am missing something, but what is wrong with the system function "isnumeric" ?
CREATE RULE [dbo].[NumericCharsOnly]
AS
ISNUMERIC(@value) = 1
GO
SQL = Scarcely Qualifies as a Language
June 8, 2009 at 11:32 am
The problem with the other solutions is the use of the '%' wild card which allows leading or trailing non-desired characters. So convert to a fixed length character type and then perform the comparison.
CREATE RULE [dbo].[NumericCharsOnly]
AS
CAST(@value as char(20)) like '[!-+0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9]' escape '!'
GO
--create a "type" , and bind the rule to teh type
CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'
GO
--a simple test table.
create table dbo.example(exampleid int identity,test numchar)
--insert some test data.
insert into dbo.example(test) values ('0000')
insert into dbo.example(test) values ('00a00') --fails! all is good
insert into dbo.example(test) values ('0000&444') --fails as expected
insert into dbo.example(test) values ('-0000') --failed when i did not want it too
insert into dbo.example(test) values ('+0000') --failed when i did not want it too
select * from dbo.example
go
drop table dbo.example
drop TYPE [dbo].[numchar]
drop RULE [dbo].[NumericCharsOnly]
SQL = Scarcely Qualifies as a Language
June 8, 2009 at 11:35 am
Carl Federl (6/8/2009)
Perhaps I am missing something, but what is wrong with the system function "isnumeric" ?CREATE RULE [dbo].[NumericCharsOnly]
AS
ISNUMERIC(@value) = 1
GO
Here's one of many problems with ISNUMERIC which should never be used as an ISALLDIGITS funtion...
SELECT ISNUMERIC('4d3')
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2009 at 11:37 am
Lowell,
The answer to your +- problem is that if you wish to include the dash in the pattern, it's easiest to do if you list it first so that it's not interpreted as a "range". Ipso-facto...
@value NOT LIKE '%[^-+0-9,]%'
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2009 at 11:49 am
Jeff Moden (6/8/2009)
Carl Federl (6/8/2009)
Perhaps I am missing something, but what is wrong with the system function "isnumeric" ?CREATE RULE [dbo].[NumericCharsOnly]
AS
ISNUMERIC(@value) = 1
GO
Here's one of many problems with ISNUMERIC which should never be used as an ISALLDIGITS funtion...
SELECT ISNUMERIC('4d3')
Thanks Jeff, I tried to answer this one, but I couldn't get anything to post.
June 8, 2009 at 12:31 pm
thanks everyone; RBarryYoung made the light go on when he said "escape" the second minus sign.
Lynn's example works, as well as using a different rule, which in turn uses a variation of a Tally example for Stripping non-Numerics.
Jeff I didn't realize the order in the LIKE would make a difference;once again, i learned something today.
it's a cheesy requirement, "varchar numeric" to support preceeding zeros, which is getting caught on the presentation layer anyway, but the idea is to error it out if it made it past that validation due to a missed coding validation.
Lowell
June 8, 2009 at 12:55 pm
Lowell (6/8/2009)
thanks everyone; RBarryYoung made the light go on when he said "escape" the second minus sign.Lynn's example works, as well as using a different rule, which in turn uses a variation of a Tally example for Stripping non-Numerics.
Jeff I didn't realize the order in the LIKE would make a difference;once again, i learned something today.
it's a cheesy requirement, "varchar numeric" to support preceeding zeros, which is getting caught on the presentation layer anyway, but the idea is to error it out if it made it past that validation due to a missed coding validation.
Thanks for the feeback, Lowell. The order only makes a difference when you don't want to take the time to escape the dash. Undocumented feature and all...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply