Ad:

MS SQL Discussion Thread
Marked Solved
Views: 3499

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Mar 4th, 2009

restrict field to alpha only

Expand Post »

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.

Similar Threads

Comment script in PHP

hkansal

Reputation Points: 10

Solved Threads: 3

Newbie Poster

Offline

21 posts
since Mar 2009

Permalink

Mar 5th, 2009

Re: restrict field to alpha only

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Mar 5th, 2009

Re: restrict field to alpha only

Thank you for the reply Geek-Master.

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

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

hkansal

Reputation Points: 10

Solved Threads: 3

Newbie Poster

Offline

21 posts
since Mar 2009

Permalink

Mar 5th, 2009

Re: restrict field to alpha only

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:

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Mar 5th, 2009

Re: restrict field to alpha only

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 7th, 2009

Re: restrict field to alpha only

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

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

hkansal

Reputation Points: 10

Solved Threads: 3

Newbie Poster

Offline

21 posts
since Mar 2009

Permalink

Mar 7th, 2009

Re: restrict field to alpha only

here is my idea then

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.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 8th, 2009

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

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Mar 8th, 2009

Re: restrict field to alpha only

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

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

hkansal

Reputation Points: 10

Solved Threads: 3

Newbie Poster

Offline

21 posts
since Mar 2009

Permalink

Mar 8th, 2009

Re: restrict field to alpha only

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

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

you can use this udf in a trigger

Last edited by dickersonka; Mar 8th, 2009 at 5:30 pm.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: how to obtain SQL Server FullText Search service?

Next Thread in MS SQL Forum Timeline: Sorting problem

DaniWeb Message

Cancel Changes

User Name
Password