How to make UDF deterministic

I have a user defined function in SQL Server 2000 that splits a Name field into First, Last, Middle names. Originally I utilized the CHARINDEX function to find the spaces in the string. Since I then found out that function is by definition Non-deterministic that didn't work for me, so I re-wrote the function a bit differently.

I am using the function as a field's formula in my table structure. I need the ability to create an index on that field so need the function to be deterministic. Apparently there is still something about how I designed the function that causes it to be non-deterministic. The function code is below; if anyone can help point me in the right direction it'd be greatly appreciated.

ALTER function fSplitName_test
(
@.name varchar(100),
@.fname int)
returns varchar(55)
as

begin
declare @.Fname_position int, @.Lname_position int, @.Mname_position int, @.SplitName varchar(55), @.iint int, @.iLen int,
@.sChar char(1), @.sName varchar(55)

set @.name = ltrim(replace(@.name, ' JR', ''))
set @.sName = @.name
set @.ilen = len(rtrim(@.name))
set @.Fname_position = 0
set @.Lname_position = 0
set @.iint = 0

while @.iint <> @.ilen
begin
set @.schar = left(@.sName, 1)
begin
if @.schar = ' '
begin
if @.Fname_position = 0
set @.Fname_position = @.iint
else
set @.Lname_position = @.iint
end
set @.iint = @.iint + 1
set @.sName = right(@.sName, @.ilen - @.iint)
end
end
if @.fname = 1
begin
if @.fname_position <> 0
set @.SplitName = left(@.name, @.Fname_position)
else
set @.splitname = 'unkn'
end
else
begin
if @.fname = 2
begin
if @.lname_position <> 0
set @.SplitName = right(@.name, @.ilen - @.Lname_position-1)
else
set @.SplitName = right(@.name, @.ilen - @.fname_position)
end
else
begin
if @.fname_position + @.lname_position <> @.ilen and @.lname_position <> 0
begin
set @.SplitName = left(@.name, @.lname_position)
set @.splitname = right(@.splitname,len(@.splitname) - @.fname_position - 1)
end
else
set @.splitname = 'unkn'
end
end
return @.SplitName
endYou might get more response if you post this under the "Microsoft SQL Server" forum. This one's for generic SQL questions - I for one know nothing about SQL Server.|||what if a person has a lastname like this "DELA CRUZ"
can you post an example of the name parameter ex: "Michael Angelo Rodriguez" or "Michael Angelo Dela Cruz" coz as you can see on the 2 ex. it is hard to detemine the first name on the first example and last name on the latter the name should have a middle initial or comma perhaps.|||This is probably completely wrong, but I wouldn't put it past SQL Server to think that you were trying to modify the parameters. That would, of course, make the function non-deterministic. Try copying them to local variables.

Otherwise, the way to debug a problem like this is to break your function into small functions and verify that the small functions are all deterministic.sql

How to make table columns invisible

Hope this is the correct forum for this question. I have set up membership and roles, and have a login function. I am using a grid view and a form view to show some information that is unique to each user. In particular, I want the grid view to show the user’s client list (client first name, last name and email), and the form view to allow input by the user of client names and emails.

I have set up the grid view and connected it to a table in my data base. This is a table that includes UserName (which is also in the aspnet_Users table), Client ID (which gets set automatically by the program), clientFirstName, ClientLastname, and ClientEmail. I have a select statement of:

SELECT * FROM [Clients] WHERE ([UserName] = @.UserName)

I have checked off “Generate Insert, Update, and Delete Statements” and optimistic concurrency.

In the grid view, I’ve enabled editing and deleting in the menu task bar, and I’ve edited the columns to make ClientID and UserName “false” for the visible property (I don’t want them to show up when the user is viewing the web page).

But the problem is, when I run the application, when I try to edit a line and hit update, it won’t take the change. This does not happen if I set the UserName visible property to “true” (and so have UserName show up).

For the form view, I similarly want to have UserName and ClientID be invisible to the user.

I'm using Visual Studio 2005 Pro, and SQL Developer Edition.

Any tips on how I might do this would be appreciated! Thanks.

Tom,

You want to post this to asp.net forum. Basically, if you just bind your data resultset to a gridview, all columns will show up. To suppress the column, you need to modify the grid's column property or don't ask for the column in your sql query.|||

OJ,

Thanks much for your reply. I'll post on asp.net. It's strange, I tried to suppress the UserName column (through using "false" for the visible property), but when I do that I'm not able to edit entries when I run the application. And if I don't ask for the UserName column with the sql query, I run into the same problem. I tried using "UserId" instead of UserName in the query, but when I ran the app I got an error message that it's not part of the profile common class. I'll keep trying, thanks again.

Tom

|||Tom,

It's worth checking out info here. There are lots of info and examples for you to pick up.
http://www.asp.net/QuickStart/aspnet/doc/ctrlref/data/gridview.aspx

g'luck.|||Great, thanks OJ! I'll delve into that and see what I can come up with.

How to locate last occurrence of character in string

charindex() function lets you find first occurrence of a particular
character in a string.
Is there similar function to find last occurrence of character in
string.
I could use reverse() or some script to find that, but it will be
inefficient.
Could anybody suggest me efficient solution for this.
Thanks and Regards,
AvinAvi, please do not multipost as I have already answered in.programming
group.
"Avi" <avimandale@.gmail.com> wrote in message
news:271710bf-a8dd-4a82-8bc2-076b920d6919@.k39g2000hsf.googlegroups.com...
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin|||On Jan 28, 5:25=A0pm, Avi <avimand...@.gmail.com> wrote:
> charindex() function lets you find first occurrence of a particular
> character in a string.
> Is there similar function to find last occurrence of character in
> string.
> I could use reverse() or some script to find that, but it will be
> inefficient.
> Could anybody suggest me efficient solution for this.
> Thanks and Regards,
> Avin
Hi Avin,
declare @.string1 varchar(50), @.string2 varchar(50)
select @.string1 =3D 'abc', @.string2=3D 'oooabcooabco'
select (datalength(@.string2)-1)-
charindex(reverse(@.string1),reverse(@.string2))
HTH

how to know time of insertion of a row

Hi
Is there a way to know time of insertion of a row without using,
timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives me
the date of insertion of a row.
--
Thanks in Advance
Regards
SuparichituduNo, there isn't
However , you are be able to create a column with GEDATE() as a DEFAULT
CONSTRAINT.
CREATE TABLE Test (col1 INT,col2 DATETIME DEFAULT GETDATE())
GO
INSERT INTO Test (col1) VALUES (100)
GO
SELECT * FROM Test
"Suparichithudu" <Suparichithudu@.discussions.microsoft.com> wrote in message
news:42420237-454F-49EE-B756-21326EAADE0C@.microsoft.com...
> Hi
> Is there a way to know time of insertion of a row without using,
> timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives
> me
> the date of insertion of a row.
> --
> Thanks in Advance
> Regards
> Suparichitudu
>|||if you're in the unenviable situation where you need to retrospectively
find this out, one thing that might be able to help you is if you can
get hold of the transaction logs over the period that you know it was
inserted. However that would require you to not be using a simple
recovery model.
There are some tools you can get free trials of which will query a
logfile, but I'm not sure of their reliability.

How to know if my varchar is only alphanumeric

Hi all
I would like to know in a select if my varchar field contains only
alphabetic caracter '
Is there a Function that can tell me this '
Thank
NicWell..you can use the IsNumeric() function like so
Select F1...Fn
From TableName
Where IsNumeric(F1) = 0
However, IsNumeric can sometimes return funky results. For example,
IsNumeric('.') returns 1.
Another way would be:
Select F1...Fn
From TableName
Where F1 Like '[^0-9]'
This expression finds results that do not contain the characters 0...9. The
downside to this approach is that a value of '02A' for example would also be
excluded even though it is not a number per se.
HTH
Thomas|||You could write one...
Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.HasNonAlpha TinyInt
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
RETURN @.HasNonAlpha
END
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Refactored...
CREATE FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
End
"CBretana" wrote:
> You could write one...
> Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
> RETURNS TinyInt
> AS
> BEGIN
> Declare @.HasNonAlpha TinyInt
> Declare @.A TinyInt
> While Len(@.Value) > 0 Begin
> Set @.A = Ascii(@.Value)
> Set @.Value = Substring(@.Value, 2, Len(@.Value))
> If @.A Not Between Ascii('a') And Ascii('z')
> And @.A Not Between Ascii('A') And Ascii('Z')
> And @.A <> Ascii(' ') Return 0
> End
> Return 1
>
> RETURN @.HasNonAlpha
> END
> "Nicolas Veilleux" wrote:
>|||"Nicolas Veilleux" <nveilleux@.nbautomation.com> wrote in message
news:u9$bVx3RFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
SELECT
CASE PATINDEX('%[^a-z ]%','This string contains the number 1')
WHEN 0 THEN 'Alphabetic'
ELSE 'Non-alphabetic'
END|||How about :
SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
If PATINDEX returns 0 then the string contains a non-alpha character.
Might need to be modified for case-sensitive collations.
- KH
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Sorry - if PATINDEX returns *NON-ZERO* then the string contains a non-alpha
character.
"KH" wrote:
> How about :
> SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
> If PATINDEX returns 0 then the string contains a non-alpha character.
> Might need to be modified for case-sensitive collations.
> - KH
>
> "Nicolas Veilleux" wrote:
>|||First of all, columns are not fields; learn to think correctly.
CHECK
( LEN
(REPLACE (
REPLACE (
. REPLACE (UPPER(foobar), 'A', '')
.
'Z', '') = LEN(foobar))
Basically, uppercase the string, use nested REPLACE() functions to turn
the alphas into striings and check the length. Do not create a
function with a WHILE loop or other procedural stuff in SQL. That
misses the whole point of a declarative language.
SQL Server has a PATINDEX() which can also be used; I am not sure which
is faster. The REPLACE () is a scan in main storage, but PATINDEX()
has to build a finite state machine.