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)
 RPAD similar function in T-SQL

Author  Topic 

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-05-13 : 07:03:05
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

735 Posts

Posted - 2005-05-13 : 07:14:20
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
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-13 : 07:17:14
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 - 2005-05-13 : 07:24:48
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

15732 Posts

Posted - 2005-05-13 : 07:57:34
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 - 2005-05-13 : 08:10:48
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

15732 Posts

Posted - 2005-05-13 : 08:13:22
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 - 2005-05-18 : 08:31:52
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

15732 Posts

Posted - 2005-05-18 : 13:09:35
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 - 2005-05-19 : 07:14:09
thank you Rob, I will try this out and get back to you.
Go to Top of Page

Igor2004
More clever than you

78 Posts

Posted - 2005-05-24 : 01:11:54
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 - 2005-06-02 : 05:41:30
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 - 2005-06-02 : 06:11:26
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 - 2005-06-02 : 06:18:05
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

15732 Posts

Posted - 2005-06-02 : 07:36:07
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 - 2005-06-02 : 07:40:35
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 - 2005-06-02 : 07:46:02
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 - 2005-06-02 : 07:56:55
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

15732 Posts

Posted - 2005-06-02 : 08:08:39
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 - 2005-06-02 : 09:53:59
its now working, i think...
bcp is hassle !
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-02 : 13:39:34
It is at first, but once you get the hang of it, you'll never use anything else.
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -