Saturday, February 25, 2012

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:

> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

No comments:

Post a Comment