function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

function

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-05-19 : 11:25:30

I am trying to create a function that will return the first two charter of last name and the first charter of first name.

For example,

Dr. John Smith --> smj
Dr. David Clark --> cld
Steve Johnson --> jos

How can I do this?

matty1stop
Starting Member

37 Posts

Posted - 2005-05-19 : 16:29:28

Is this all in the same field? It would be better design to separate your names into different fields, otherwise its almost impossible to differentiate between the first last and possibly middle names unless you have some pretty heavy constraints on the front end that would probably be impracticle given the variations of formats that names can take.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 23:32:24

CREATE FUNCTION dbo.GetChar (
@lastname as varchar(150),
@firstname as varchar(150)
)
RETURNS varchar(3) AS
BEGIN
DECLARE @retChar varchar(3)
set @lastname = lower(@lastname)
set @firstname = lower(@firstname)
--- set delimiters characters to be ignored
--- ex. one set for DR.
DECLARE @delimiter nvarchar(10)
set @delimiter = 'DR.'

-- variable to be trimmed/cleaned ex. @firstName -- because it is where DR. is located
set @firstname =Ltrim(replace(@firstname, @delimiter,''))
--- end set
set @retChar = (case
WHEN len(@lastname) < 2 THEN ''
ELSE substring(@lastname,1,2) +
(case
when len (@firstname) < 1 THEN ''
else substring(@firstname,1,1)
END
)
END)
RETURN @retChar

END

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 23:36:00

user this instead.... added trim

CREATE FUNCTION dbo.GetChar (
@lastname as varchar(150),
@firstname as varchar(150)
)
RETURNS varchar(3) AS
BEGIN
DECLARE @retChar varchar(3)
set @lastname = Ltrim(lower(@lastname))
set @firstname =Ltrim(lower(@firstname))
--- set delimiters characters to be ignored
--- ex. one set for DR.
DECLARE @delimiter nvarchar(10)
set @delimiter = 'DR.'

-- variable to be trimmed/cleaned ex. @firstName -- because it is where DR. is located
set @firstname =ltrim(replace(@firstname, @delimiter,''))
--- end set
set @retChar = (case
WHEN len(@lastname) < 2 THEN ''
ELSE substring(@lastname,1,2) +
(case
when len (@firstname) < 1 THEN ''
else substring(@firstname,1,1)
END
)
END)
RETURN @retChar

END

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-20 : 03:47:26

If the data is all in one field I have some functions that may help with some tweaking

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50044[/url]
steve

A sarcasm detector, what a great idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources