restrict field to alpha only

[hkansal]

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

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.

[hkansal]

Thank you for the reply Geek-Master. :)

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

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

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:

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.

[dickersonka]

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

[hkansal]

@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:

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

[dickersonka]

here is my idea then

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

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.

[hkansal]

@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:

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 :P) 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

[dickersonka]

Ahhh sorry, forgot you only needed sql server

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

here it is with modification

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

you can use this udf in a trigger

[hkansal]

Thank You Everyone!

@dickersonka:
Thank you for a possible solution. You gave me an idea of how we can achieve the desired condition.
Actually I simplified it as:

CREATE TABLE tbMyTable(
        onlyAlphaField      VARCHAR(40)
                                    CHECK     ( patindex('%[^a-zA-Z]%', onlyAlphaField) = 0)
)

This way the constraint checks if anything other than alphabets is present.

@Geek-Master:
Thank you for the MSDN reference. I ll zero in to understand the use of RegEx for Sql Server.

We can now declare the topic as solved. Thank you experts, for the active follow-up.

Thank You.

Regards,
HKansal