SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Help Creating Script with limited characters		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbeginner123
Starting Member

9 Posts
Posted - 01/22/2013 :  15:01:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 01/22/2013 :  15:21:41  Show Profile  Reply with Quote
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

India
48092 Posts
Posted - 01/23/2013 :  01:23:21  Show Profile  Reply with Quote 

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))))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000