Return persons initials
-
Hi All
SQL Server 2000
I have a table as follows...
personId int
person name varchar 50
Data as follows...
ID Name Result Required from Query
1 Mr BG Bloggs BGB
2 Rev JDAL Smith JDALS
3 Dr J Brown JB
4 Mrs Jones J
5 Ms Sue A Smith SAS
I would like to return the persons full initials (as in the Result Required from Query).
I probably need to do it as a UDF, but can't get it working quite right. Any suggestions most welcome.
Thanks
CCB
-
I don't think there is a way to accomplish this?
How will SQL Server distinguish between "Sue" and "JDAL"?
N 56°04'39.16"
E 12°55'05.25" -
try this
CREATE FUNCTION dbo.Initials
(
@STR VARCHAR(4000)
)
RETURNS VARCHAR(4000) AS
BEGIN
DECLARE
@STR_INIT VARCHAR(4000),
@STR_PROCESS VARCHAR(4000),
@STR_COMPLETE VARCHAR(4000),
@I INT,
@j-2 INT
SET @STR_INIT =LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@STR,'Mrs',''),'Mr',''),'Dr',''),'Rev',''),'Ms',''))
SET @I=DATALENGTH(@STR_INIT)
SET @j-2=0
SET @STR_COMPLETE=''
WHILE @j-2<@I
BEGIN
SET @STR_PROCESS= SUBSTRING(@STR_INIT,@J,1)
IF (ASCII(@STR_PROCESS)>64 AND ASCII(@STR_PROCESS)<92)
BEGIN
SET @STR_COMPLETE= @STR_COMPLETE+@STR_PROCESS
END
SET @j-2=@J+1
END
RETURN (@STR_COMPLETE)
END
----------------------------------------------
If you have any other titles before Name add those with replace function to the @STR_INT
call this function like this
select dbo.Initials(personname) from person
hope this will help you
-
Replace the following statement in the function
Added space to every title
SET @STR_INIT =LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@STR,'Mrs ',''),'Mr ',''),'Dr ',''),'Rev ',''),'Ms ',''))
This will guarantee you, if any person have the name with the title s in it
-
I tried it a bit different than Gopi Nath Muluka by adding a table with the "prefix" values and using two functions, (one to remove those values rather than directly hardcode them into the function hoping for more flexibility and the second to get the upper case characters). I imagine someone can refine this...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.RidPrefix') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.RidPrefix
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.RidPrefix( @ValueToAlter AS varchar(25)) RETURNS varchar(25)
AS
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, Prefix, '') FROM RemoveCharacters
RETURN( @ValueToAlter )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.UpperCaseCharacters') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.UpperCaseCharacters
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[RemoveCharacters]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[RemoveCharacters]
GO
CREATE TABLE dbo.RemoveCharacters( Prefix varchar(10))
INSERT INTO RemoveCharacters
SELECT 'Mr ' UNION ALL
SELECT 'Rev ' UNION ALL
SELECT 'Dr ' UNION ALL
SELECT 'Mrs ' UNION ALL
SELECT 'Ms '
-------------------------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.GetUpperCase') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.GetUpperCase
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.GetUpperCase( @ValueToAlter AS varchar(25)) RETURNS varchar(25)
AS
BEGIN
DECLARE @Position integer,
@AlteredValue varchar(25)
SET @Position = 1
SET @AlteredValue = ''
WHILE @Position < LEN( @ValueToAlter)
BEGIN
SELECT @AlteredValue = @AlteredValue + CASE
WHEN ASCII( SUBSTRING( @ValueToAlter, @Position, 1)) BETWEEN 65 AND 90
THEN SUBSTRING( @ValueToAlter, @Position, 1)
ELSE ''
END
SELECT @Position = @Position + 1
END
RETURN( @AlteredValue )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------------------
DECLARE @Person TABLE( ID integer, [Name] varchar(25))
INSERT INTO @Person
SELECT 1, 'Mr BG Bloggs' UNION ALL
SELECT 2, 'Rev JDAL Smith' UNION ALL
SELECT 3, 'Dr J Brown' UNION ALL
SELECT 4, 'Mrs Jones' UNION ALL
SELECT 5, 'Ms Sue A Smith'
-------------------------------------------------------------------------------------------------------------------
SELECT dbo.GetUpperCase( REPLACE( dbo.RidPrefix( [Name]), CHAR(32), ''))
FROM @Person
I wasn't born stupid - I had to study.
-
I hope you were not thinking about me... my numbers table is retired for the moment... Looks like you'll have to think about the other solution on your own this time
. -
I didn't get you.. what does it mean?
-
I was refferring to this from Farrell's post : case characters). I imagine someone can refine this...
I used to always change his bulldozer approach to a set based solution. However in this case it probabely would not make a huge difference.
-
Other than the loop, it is pretty set-based. Good to hear from you again, Mr. Smarty-pants!
I wasn't born stupid - I had to study.
-
"Good to hear from you again, Mr. Smarty-pants!
"I wasn't studying stupidity at school... maybe I should have taken some extras
. -
You have performed beyond my wildest expectations for not having studied!!!
I wasn't born stupid - I had to study.
-
I had many good teachers on that matter after school. But none better than yours truly
. -
Thanks Guys.
Went with Gopi Nath Muluka's suggestion in the end.
CCB
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply