MS SQL

MS SQL RSS

restrict field to alpha only

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Mar 2009

Posts: 21

Reputation: hkansal is an unknown quantity at this point

Solved Threads: 3

hkansal

Offline

Newbie Poster

restrict field to alpha only

Mar 4th, 2009

Hello Experts,

I am fairly new to MS SQL Server 2005. I am trying to create a table where a column has the restriction of allowing alphas only.

I know how to achieve this in Oracle using String functions. But am not able to do so in MSSql.

The replace() function searches for the complete search_string to be present, as oppososed to Oracle's translate() function.
Please give me a hint if this can be achieved through inbuilt functions.

Any pointers towards using RegEx are also requested, if the same can be achieved using RegEx.

PS: I have a remote ms sql 2005 db, which I would access through java.

Thank you.

Regards,
HKansal

Last edited by hkansal; Mar 4th, 2009 at 10:46 am.

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: restrict field to alpha only

Mar 5th, 2009

Most likely you will not be able to do this using a simple insert or update sql statement, so you will need to create a stored procedure to handle this task. I'm not exactly sure what functions exist to detect non-alpha values, but most likely you will want to roll-back any request that doesn't match your criteria. Since you will be interfacing the database using Java, I would recommend putting the validation code in Java.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Mar 2009

Posts: 21

Reputation: hkansal is an unknown quantity at this point

Solved Threads: 3

hkansal

Offline

Newbie Poster

Re: restrict field to alpha only

Mar 5th, 2009

Thank you for the reply Geek-Master.

If I elaborate, I would like to learn something to facilitate my task at DDL. Eg:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TABLE tbMyTable(
        onlyAlphaField      VARCHAR(40)
                                    CHECK     onlyAlphaField     
                                    LIKE  'expression'
)
CREATE TABLE tbMyTable(
        onlyAlphaField      VARCHAR(40)
                                    CHECK     onlyAlphaField     
                                    LIKE  'expression'
)

Yes, via java I can and have implemented the requirement and I can ensure no mistake is made. But let us say I have no java or anything else at hand, only the db.

I would like to learn doing this.

Thank You.

Regards,
HKansal

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: restrict field to alpha only

Mar 5th, 2009

I haven't ever looked at creating constraints yet, most of my time has been looking at maintenance plans. So this is still new to me, but all of the examples I have seen are a static pattern. None have shown a way to just say only allow these kinds of characters for the amount of characters your column has reserved like varchar(30). The check or rule constraints have been built like this for emails:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
ALTER TABLE MyTable
ADD CONSTRAINT checkEmail 
CHECK (Email LIKE '%@%.[a-z][a-z][a-z]');
GO
ALTER TABLE MyTable
ADD CONSTRAINT checkEmail 
CHECK (Email like '%@%.[a-z][a-z][a-z]');
GO

The pattern expression in this example stats that any character + a dot + any character + another dot and then only allow 3 alpha character values. Pretty much static.

I would really like to know this myself, so I'm going to keep looking while you do. Hopefully one of us can find the answer.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: restrict field to alpha only

Mar 5th, 2009

would adding a trigger that will format the field to only be characters upon inserts and updates be sufficient?

Custom Application & Software Development
www.houseshark.net

•

Join Date: Mar 2009

Posts: 21

Reputation: hkansal is an unknown quantity at this point

Solved Threads: 3

hkansal

Offline

Newbie Poster

Re: restrict field to alpha only

Mar 7th, 2009

@Geek-Master:
Then we are onto it. I ll make all the efforts to find the solution. You too please try consulting ppl.

@dickersonka:
Thanks for coming up with an approach. Ok, even if a trigger is to be used I am fine with it. But in that trigger too, we ll need some String manipulaion, right? Please tell me if you have something in mind. It ll help us learn.

eg:

if I write:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TABLE tb(
   onlyAlphaField varchar(200) CHECK (onlyAlphaField LIKE '[a-b][a-b]')
);
create table tb(
   onlyAlphaField varchar(200) check (onlyAlphaField like '[a-b][a-b]')
);

The above DDL would restrict values in the filed to 2 alphabets only, and only alphabets.

So, might be we can make something as generic for the 200 spaces of varchar field. Don't expect me to write [a-b] 200 times.

Thank You.

Regards,
HKansal

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: restrict field to alpha only

Mar 7th, 2009

here is my idea then

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT translate('123AB45', 
         'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 
         'ABCDEFGHIJKLMNOPQRSTUVWXYZ') FROM dual;
select translate('123AB45', 
         'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 
         'ABCDEFGHIJKLMNOPQRSTUVWXYZ') from dual;

i don't necessarily know the context of how you need this, but this will replace all numeric values with '' and leave your string 123AB45 as AB

Last edited by dickersonka; Mar 7th, 2009 at 6:19 pm.

Custom Application & Software Development
www.houseshark.net

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Using CLR for Regular Expressions...or do we have to?

Mar 8th, 2009

Personally, I would refrain from using any data transformations in this manner. Changing the data being inserted by the user does comply with the check constraint, but it alters what the user is expecting to have been saved to the database. I can see why you were hesitant, dickersonka, with using this method for this situation.

I did find an article in regards to using regular expressions, which the article states is beyond what T-SQL can handle. So you end up using CLR instead, like VB or C#, to handle these kinds of tasks. The article can be found here:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

However, I'm still confused as to why T-SQL can't do this or why I haven't seen any examples yet as to say CHECK for only alpha characters. This sort of functionality would be great for Name fields where you know a number or special character should not go. Using CLR seems to be going overboard, but it just may be that this is the only option in this version of SQL, but I believe it is still to early to say SQL 2005 can't handle this out of the box.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Mar 2009

Posts: 21

Reputation: hkansal is an unknown quantity at this point

Solved Threads: 3

hkansal

Offline

Newbie Poster

Re: restrict field to alpha only

Mar 8th, 2009

@dickersonka:
Thanks for sharing your idea. I am glad you understand what I am actually looking for.
The "translate()" function, as you wish to, works with Oracle, but MS SQL server does not have that inbuilt function. Instead, as I had said in my first post, it has a "replace()" function which works a bit differently.
Eg:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT REPLACE('12345','123','');
Output
---------
45
 
SELECT REPLACE('123,'13','');
Output
---------
12345
 
select replace('12345','123','');
Output
---------
45

select replace('123,'13','');
Output
---------
12345

So I am still on a lookout for something to work as we want.

@Geek-Master:
Yes, as you said, it is not good to fiddle with user inputs and you identified that sometimes business logic might ask for it(eg: Name field), I want my DB to be robust enough to resist faulty inputs if my API misses it by any chance or some other malicious reasons.

As you talked of CLR, then yes that can be done using CLR(that is the only thing I know about it

) but I am not sure how to. So I am trying to follow MSDN's RegEx path and an example on adding RegEx to the DB.

It would have made life easy if something like "translate()" would have been available. I am trying my best to find a way.

Thank You.

Regards,
HKansal

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: restrict field to alpha only

#10

Mar 8th, 2009

Ahhh sorry, forgot you only needed sql server

here's a link that should help
http://www.nigelrivett.net/SQLTsql/R...haracters.html

here it is with modification

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE FUNCTION dbo.AlphaOnly(@value varchar)
RETURNS VARCHAR
AS
BEGIN
DECLARE @returnVar varchar, @i int
SET @returnVar = @value
 
	SELECT @i = patindex('%[^a-zA-Z]%', @returnVar )
	while @i > 0
	begin
		SELECT  @returnVar  = REPLACE(@returnVar, substring(@returnVar , @i, 1), '')
		SELECT @i = patindex('%[^a-zA-Z]%', @returnVar)
	end
 
RETURN @returnVar
 
END
CREATE FUNCTION dbo.AlphaOnly(@value varchar)
RETURNS VARCHAR
AS
BEGIN
DECLARE @returnVar varchar, @i int
SET @returnVar = @value

	select @i = patindex('%[^a-zA-Z]%', @returnVar )
	while @i > 0
	begin
		select  @returnVar  = replace(@returnVar, substring(@returnVar , @i, 1), '')
		select @i = patindex('%[^a-zA-Z]%', @returnVar)
	end

return @returnVar

END