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
 Transact-SQL (2000)
 RPAD similar function in T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jamie
Aged Yak Warrior

542 Posts

Posted - 05/13/2005 :  07:03:05  Show Profile  Reply with Quote
hi,
is there a similar function in SQL server to ORACLES RPAD ?

I need to display a field for a fixed width of 10.
if the field is not 60 then add blank spaces . .
eg:

[jamie ]
[sqlteam ]
[transact-s]

and so on . .. .
thank you for any help/

JAme

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 05/13/2005 :  07:14:20  Show Profile  Reply with Quote
Setting the column to CHAR/NCHAR(60) will add trailing spaces to that length. Alternatively, you could convert in your select if you don't want to mess with your tables.
If you want a function to do it, you can get the same result with a combination of LEN and REPLICATE, although it seems like a bit of hard work!

Mark

Edited by - mwjdavidson on 05/13/2005 07:15:50
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/13/2005 :  07:17:14  Show Profile  Reply with Quote
display should be done on the client. The actual data should not be altered to fit a display.

Bypassing that, you can alter your column from a Varchar(10) to a Char(10). That seems to pad it to the right with spaces. One of the experts can tell you if this is a good idea or not. (no coffee yet)
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 05/13/2005 :  07:24:48  Show Profile  Reply with Quote
hi, thanks for the suggestions.
when I say display, what I really mean is i need to output to a text file with fixed widths.
I have been playing around with len and came up with this :

case
when len(Field1) > 10 then
substring(Field1,1,10)
else
Field1 + SPACE (10 - len(Field1)) end AS Field1

instead of typing this out everytime, hoe can I make it into a function, so then all I need to do is :

functionname(field1,10) as field1,
functionname(field2,40), as field2

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 05/13/2005 :  07:57:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
bcp can export fixed-width files using a format file. You can find more information about both in Books Online. bcp can even generate the format file for you.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 05/13/2005 :  08:10:48  Show Profile  Reply with Quote
hi Rob,
I am actually using bcp to export the data.


DECLARE @FileName varchar(100),
@bcpCommand varchar(2000)

SET @FileName = REPLACE(testdata_'+CONVERT(varchar,GETDATE(),111),'/','')+'_'+REPLACE(CONVERT(varchar,GETDATE(),8),':','')+'.txt'

SET @bcpCommand = 'bcp "select * from TABLE" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'

EXEC master..xp_cmdshell @bcpCommand

GO

but this just output the data as it is in the table.
problem is my data is currently at one fixed width, but need to export to different fixed widths.

eg,

forename currently varchar(100)
forename needs to export to varchar(60)

surname currently varchar(100)
surname needs to export to varchar(80)

DOB currently datetime
DOB needs to exprt to varchar(10)

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 05/13/2005 :  08:13:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can still do this with a format file. Once you generate the format file you can edit it and change the column widths to fit the size you need.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 05/18/2005 :  08:31:52  Show Profile  Reply with Quote
hi Rob, i really can't find how to do this ,....
is it a bcp command ?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 05/18/2005 :  13:09:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
First thing to do, run your bcp command from the command line, NOT from xp_cmdshell. DO NOT specify the -c parameter. You'll be prompted interactively for data types, prefix and column lengths, and column terminators. Accept the defaults on everything, except make sure that all data types are specified as CHAR. If you are prompted with a different datatype for a column, enter "char".

When you are prompted at the end to save it to a format file, answer yes and provide a name for it. Once that format file is saved, open it in notepad. Compare it to the example listed in Books Online to get an idea of its layout. If you specified column terminators, remove them except for the last one, which is the row terminator. Make sure to keep the layout aligned as it was originally (I've had some format files blow up if the alignment was off, YMMV).

When you run bcp in your stored procedure, use the -f parameter and specify the name and path of your format file instead of -c. You should get a nice fixed-width file from it. If you still have problems, post the contents of the format file you used to export it.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 05/19/2005 :  07:14:09  Show Profile  Reply with Quote
thank you Rob, I will try this out and get back to you.
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 05/24/2005 :  01:11:54  Show Profile  Visit Igor2004's Homepage  Reply with Quote
RPAD similar function in T-SQL
PADR is RPAD
PADL is LPAD
INITCAP is PROPER etc
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side

select dbo.PADR('Your string ', 60, default)
*-----------------------------------------------------------------
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48509
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49509


*-----------------------------------------------------------------
robvolk
SQL Server MVP & SQLTeam MVY


USA
9885 Posts
Posted - 05/09/2005 : 23:53:30
----------------------------------------------------------------------
How about not cross-posting (again), since you're not actually posting the code in these forums?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48509
----------------------------------------------------------------------


User-Defined string Functions Transact-SQL   
Ladies and Gentlemen,

I would like to offer you the following string functions Transact-SQL

GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...

More than 2000 people have already downloaded my functions. I hope you will find it useful as well.

For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115

With the best regards.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  05:41:30  Show Profile  Reply with Quote
Hi Rob, I have finnally created a format file !
when I run the bcp command now I recieve the error :
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file


my format file is like

8.0
5
1 SQLCHAR 0 50 "none" 1 [Block External system ID] ""
2 SQLCHAR 0 30 "none" 2 [Location Name] ""
3 SQLCHAR 0 8 "none" 3 [Location Reference] ""
4 SQLCHAR 0 9 "none" 4 [Floor Area] ""
5 SQLCHAR 0 2 "none" 5 [Floor Level] ""


my command is :

DECLARE @FileName1 varchar(100),
@FileName2 varchar(100),
@bcpCommand1 varchar(2000)

SET @FileName1 = 'e:\locations.txt'
SET @FileName2 = 'e:\Locations.fmt'

SET @bcpCommand1 = 'bcp "SELECT * FROM Data.dbo.ROOM" queryout "'
SET @bcpCommand1 = @bcpCommand1 + @FileName1 + '" -f"' + @FileName2 + '" -Usa -Ppass'

EXEC master..xp_cmdshell @bcpCommand1

GO


can you see any problems with this ?
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  06:11:26  Show Profile  Reply with Quote
could this be failing because I am truncating some fields ?

I need to make fixed widths.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  06:18:05  Show Profile  Reply with Quote
jst check max length of fields and none are truncating.
the number of fields shown fro mthe select statment is 5.
the number on the format file is 5 !
how come bcp thinks the number is different....
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 06/02/2005 :  07:36:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Did you modify a generated file or create it from scratch? I don't recommend creating them from scratch, always generate one and then modify it. Don't ask me why it matters, I've just never had any success creating them from scratch.

I'd suggest changing the column names to something simple, remove spaces and square brackets. These are not real column names anyway, they don't have to match what's in the table.

Also, replace "none" with "" (empty string) for column terminator. You also must make the last column terminator "\r\n" otherwise the file will be one unbroken string.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  07:40:35  Show Profile  Reply with Quote
I generated the format file, then modified ti.
I'll change the field names and try that....
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  07:46:02  Show Profile  Reply with Quote
brillient, I know havce the error :
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file

I changed the field names ( removed spaces ).
also added "" instead of "none" and "\r\n" for the last one.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  07:56:55  Show Profile  Reply with Quote
sorry for breing such a pain !
I have recreated the format file after changing the field names inthe query.

when I run it know I get the error :
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (19 bytes) exceeds host-file field length (9 bytes) for field (4). Use prefix length, termination string, or a larger host-file field size. Truncation cannot occur for BCP output

how can I make a fixed width text file if I can't truncate data ?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 06/02/2005 :  08:08:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
You have data in the table that is longer than 9 bytes. You'd either have to modify the format file, alter the table to hold only 9 characters in that column, or use a query or view with a LEFT() or SUBSTRING() on that column to return only the first 9 characters when you do the bcp operation.
Go to Top of Page

jamie
Aged Yak Warrior

542 Posts

Posted - 06/02/2005 :  09:53:59  Show Profile  Reply with Quote
its now working, i think...
bcp is hassle !
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 06/02/2005 :  13:39:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
It is at first, but once you get the hang of it, you'll never use anything else.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.16 seconds. Powered By: Snitz Forums 2000