Formatting Name (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)

Formatting Name (Function)

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-13 : 21:35:07

Below shown is a function which will parse
Declare @full_name varchar(100)
DECLARE @name_separator char(1)
SET @name_separator ='#'
SET @full_name = 'Alex#Joseph#K'

select dbo.udf_dd_format_name_lfm_s(@full_name,'#',1) as first_name
select dbo.udf_dd_format_name_lfm_s(@full_name,'#',2) as middle_name
select dbo.udf_dd_format_name_lfm_s(@full_name,'#',3) as last_name

The above select will give me the correct parsed names.Now the business wants something where they have the same name
SET @full_name = 'Alex#Joseph#K'

But the last_name will be 'Alex',
first_name will be 'Joseph' and
middle_name will be 'K'

Basically when they pass the @extracted_position = 1 it should return
the value from the first_position and 2 will return from the second postion and 3 will return from the third postion.So that if they can pass 1 and assign the name to last_name instead of first_name...

Can somebody help me make this intelligent function algorithm


CREATE FUNCTION [dbo].[udf_dd_format_name_lfm_s]
 	(
	@full_name VARCHAR(120),
	@name_separator CHAR(1),
	@extracted_position INT
	)  
	RETURNS VARCHAR(100) AS  
	BEGIN 
		DECLARE @first_name varchar(100)
		DECLARE @middle_name varchar(100)
		DECLARE @last_name varchar(100)
		DECLARE @extracted_name VARCHAR(100)
		
		SELECT @first_name=SUBSTRING(@full_name,0,CHARINDEX(@name_separator,@full_name))
		SELECT @full_name=REPLACE(@full_name,@first_name+@name_separator,'')
		SELECT @middle_name=REPLACE(SUBSTRING(@full_name,0,CHARINDEX(@name_separator,@full_name)),@name_separator,'')
		SELECT @last_name=REPLACE(REPLACE(@full_name,@middle_name+@name_separator,''),@name_separator,'')
		IF @last_name=''
			BEGIN
				SET @last_name =@middle_name
				SET @middle_name=''
			END
		IF @extracted_position = 1 
			BEGIN
				IF @first_name=''
					SET @extracted_name=NULL
				ELSE
					SET @extracted_name=@first_name
			END
		ELSE
			IF @extracted_position = 2 
				BEGIN
					IF @middle_name=''
						SET @extracted_name=NULL
					ELSE
						SET @extracted_name=@middle_name
				END
			ELSE
				IF @extracted_position = 3 
					BEGIN
						IF @last_name=''
							SET @extracted_name=NULL
						ELSE
							SET @extracted_name=@last_name
					END
		
		RETURN @extracted_name
	END