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
 Import/Export (DTS) and Replication (2000)
 Help Creating Script with limited characters

Author  Topic 

sqlbeginner123
Starting Member

9 Posts

Posted - 2013-01-22 : 15:01:08
I need help creating a script with a limit of 40 characters and I need for there to be a space between the first and last name.

My current script reads like this:

(right(((replace((Upper(ps.sfirstname)),'','')) + (replace((Upper(ps.ulastname)),'','')))+ replicate(' ', 40 - LEN((ps.sfirstname) + (ps.ulastname))),40))+

The problem is that it isn't adding in spaces to pad up to 40 characters and also I need a space between the first and last name.

My results look like this:

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJANEDOE I

I need for it to look like

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXJANE DOE I

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 15:21:41
I did not follow the logic you are trying to implement, especially the replacing of '' with ''. But, wouldn't the following do what you are trying to do?
RIGHT(REPLICATE(' ',40) + UPPER(sfirstname) + ' ' + UPPER (ulastname), 40)
Are you handling the cases where total length of firstname + space + lastname would exceed 40 characters?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:23:21
[code]
DECLARE @FirstName varchar(20),@LastName varchar(20)
SELECT @FirstName='Mary',@LastName='Elizabeth'

SELECT STUFF(UPPER(@FirstName) + ' ' + UPPER (@LastName),1,0,REPLICATE(' ',40-LEN(UPPER(@FirstName) + ' ' + UPPER (@LastName))))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -