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.
| 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 II 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? |
 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Help Creating Script with limited characters