column that can only take Alphanumerics? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

column that can only take Alphanumerics?

Author

Topic

tonyclifton
Starting Member

12 Posts

Posted - 2009-10-07 : 14:57:29

I've found this very neat function online and I want this function to work with my add-procedure for table customer, so that for example a "name" won't take rubbish inserts.


ALTER FUNCTION [dbo].[fn_ReplaceNotAlfaChar] ( @STR VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS 
    BEGIN
        IF PATINDEX('%[^a-z ]%', @STR) = 0 
            RETURN @STR
        WHILE PATINDEX('%[^a-z ]%', @STR) > 0 
            SELECT  @STR = STUFF(@STR, PATINDEX('%[^a-z ]%', @STR), 1, ' ')
        RETURN @STR
    END

the add procedure has a normal insert statement like this.


INSERT  INTO Customer
                (
                  
                  Name,
                  FirstName,
                  ....
                )
        VALUES  (
                   @Name,
                  @FirstName,
                  ....
                )

        COMMIT TRANSACTION

Could I place the function inside this procedure to handle inputs or do I need a trigger to call the function when a name was inserted?

But maybe there are other ways to deal with this, so what's a good solution and how can I achieve it?

Thanks for reading.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-07 : 15:18:25

It looks like you might be using a stored procedure. If so, you can create a new variable to hold the value and run your function on the incoming parameter and assign it to the new variable and use that variable for you insert. I'd do it that way so that you can do validation and raise the appropriate error message (like if someone enters 123456 or something).

Alternatively, you can just do it inline and let it rip for example:

INSERT  INTO Customer
                (
                  
                  Name,
                  FirstName,
                  ....
                )
        VALUES  (
                   fn_ReplaceNotAlfaChar(@Name),
                  @FirstName,
                  ....
                )

        COMMIT TRANSACTION

EDIT: Fat fingered some stuff. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources