January 13, 2012 at 1:38 pm
how can i create a udf or stored procedure to check if given SSN pattern is correct or not
ssnno(123-456-7890)
123-456-7890 - correct
123-4567890 - not correct
123456-7890 - not correct
1234567890 - not correct
123\456&8907 - not correct
January 13, 2012 at 1:48 pm
As far as the contents of a function, or stored procedure, it would seem that the LIKE comparison operator is what you need.
CASE
WHEN SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN 'valid'
ELSE 'invalid'
END
January 13, 2012 at 2:30 pm
This is definitely a case where I'd use a function not an SP. Phil has provided the code you'd be looking for.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 13, 2012 at 2:39 pm
Create function: http://msdn.microsoft.com/en-us/library/ms186755.aspx
Wrap that around phil's code.
January 13, 2012 at 3:34 pm
Hi Jack,
How can we decide what object should we use? Just like, you said in this case you will go with function instead of stored procedure.
Thanks
January 13, 2012 at 4:23 pm
It really depends on how it is going to be used. This one sounds like boolean check something like:
IF dbo.IsValidSSN(@SSN) = 1
BEGIN
-- Do something
END
ELSE
BEGIN
RAISERROR('Invalid Social Security Number', 16, 1)
END
To do that with a stored procedure I'd have to have an OUTPUT parameter, etc...
In general if I'm returning a result to a client I'd do an SP, if I'm going to use it in a QUERY then it is probably better as a function. You do have to be careful with functions because they can be hidden RBAR (loops) and not truly set-based so I don't use a ton of functions. I think they are good in theory, but often not as good in practice. Code re-use isn't always the path to best performance in a database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 13, 2012 at 11:32 pm
There are two ways to write this as a function, as a scalar function or an inline table valued function. You may actually want it both ways. One can be used as soon in Jack's post, the other as part of a CROSS APPLY in a from clause.
Here are the two ways I wrote the function using the code provided in this thread.
CREATE FUNCTION dbo.isValidSSN (@SSSNO CHAR(11))
RETURNS VARCHAR(7)
AS
BEGIN
RETURN(
CASE
WHEN @SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN 'valid'
ELSE 'invalid'
END
)
END;
CREATE FUNCTION dbo.ValidSSN( @SSSNO CHAR(11))
RETURNS TABLE
AS
RETURN(
SELECT
CASE
WHEN @SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN 'valid'
ELSE 'invalid'
END AS ValidSSN
);
January 13, 2012 at 11:39 pm
Jack Corbett (1/13/2012)
You do have to be careful with functions because they can be hidden RBAR (loops) and not truly set-based so I don't use a ton of functions. I think they are good in theory, but often not as good in practice. Code re-use isn't always the path to best performance in a database.
Scalar functions are certainly best avoided. Multi-statement functions have occasional good uses but are again often poorly used. In-line functions are expanded into the query text just like a parameterized view would be:
CREATE FUNCTION dbo.IsValidSSN
(
@Input char(11)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CASE
WHEN @Input LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN CONVERT(bit, 'true')
WHEN @Input NOT LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN CONVERT(bit, 'false')
ELSE
CONVERT(bit, NULL)
END AS IsValid;
-- Just the same as writing the body of
-- the function out by band
SELECT
ivs.IsValid
FROM dbo.IsValidSSN('123-45-6789') AS ivs;
GO
SELECT
Test.data,
ivs.IsValid
FROM
(
VALUES
('123-45-6789'),
('000-00-000'),
('abc-de-fghi'),
('987-98-9876')
) AS Test (data)
CROSS APPLY dbo.IsValidSSN(Test.data) AS ivs
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2012 at 11:46 pm
I see Lynn posted while I was writing that. In an attempt to add some value, here's a possible procedure implementation. I don't like this as much overall:
CREATE PROCEDURE dbo.IsValidSSN
@Input char(11),
@IsValid bit OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET @IsValid =
CASE
WHEN @Input LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN CONVERT(bit, 'true')
WHEN @Input NOT LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'
THEN CONVERT(bit, 'false')
ELSE
CONVERT(bit, NULL)
END;
END;
GO
DECLARE @ValidSSN bit;
EXECUTE dbo.IsValidSSN
@Input = '123-45-6789',
@IsValid = @ValidSSN OUTPUT;
SELECT @ValidSSN;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 14, 2012 at 2:09 am
Just as an aside , you may prefer to add the code to a constraint.
http://msdn.microsoft.com/en-us/library/ms179491.aspx
That way , if you have multiple ways data ( different procs, an ssis task or two etc..) can be inserted, then the database will ensure the data is in the correct format.
January 14, 2012 at 5:42 am
This is a good option, so long as the temptation to use a scalar function in the constraint definition is resisted. Write the condition out in full in the CHECK constraint:
DECLARE @SSNs TABLE
(
SSN char(11) NOT NULL
CHECK (SSN LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]')
)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 14, 2012 at 6:09 am
SQL Kiwi (1/14/2012)
This is a good option, so long as the temptation to use a scalar function in the constraint definition is resisted. Write the condition out in full in the CHECK constraint:
DECLARE @SSNs TABLE
(
SSN char(11) NOT NULL
CHECK (SSN LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]')
)
I'd actually recommend doing both a function and this constraint. I'd do the function to verify data BEFORE the insert or update into the table is done. I'd call this defense in depth. You are verifying data as part of the business process and then you are enforcing the integrity of the data with the check constraint in case there are instances where the data is touched outside the application, like a DBA doing an insert or update (not that I've ever done this :-D).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 14, 2012 at 8:23 am
Jack Corbett (1/14/2012)
I'd actually recommend doing both a function and this constraint. I'd do the function to verify data BEFORE the insert or update into the table is done. I'd call this defense in depth. You are verifying data as part of the business process and then you are enforcing the integrity of the data with the check constraint in case there are instances where the data is touched outside the application, like a DBA doing an insert or update (not that I've ever done this :-D).
Yes, I like my constraints to be as close to the data as possible too. It's a shame CHECK constraints cannot use in-line functions, otherwise we could use that rather than duplicating the CHECK logic on any table with an SSN. Of course the format of SSNs doesn't change much, but there are other cases where the CHECK logic might be changed from time to time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply