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 |
|
|
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) |
|
|
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 :casewhen len(Field1) > 10 thensubstring(Field1,1,10)elseField1 + SPACE (10 - len(Field1)) end AS Field1instead 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 |
|
|
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. |
|
|
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 @bcpCommandGObut 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 datetimeDOB needs to exprt to varchar(10) |
|
|
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. |
|
|
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 ? |
|
|
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. |
|
|
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. |
|
|
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 LPADINITCAP is PROPER etcPADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left sidePADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right sideselect dbo.PADR('Your string ', 60, default)*-----------------------------------------------------------------http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48509http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49509*-----------------------------------------------------------------robvolkSQL Server MVP & SQLTeam MVYUSA9885 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-SQLGETWORDCOUNT() Counts the words in a stringGETWORDNUM() Returns a specified word from a stringAT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost characterRAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character stringOCCURS() Returns the number of times a character expression occurs within another character expressionPADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left sidePADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right sidePADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sidesPROPER() Returns from a character expression a string capitalized as appropriate for proper namesRCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the rightARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expressionROMANTOARAB() 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 thehttp://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115Please, download the filehttp://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115With the best regards. |
|
|
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-filemy format file is like8.051 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 @bcpCommand1GOcan you see any problems with this ? |
|
|
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. |
|
|
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.... |
|
|
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. |
|
|
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.... |
|
|
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 fileI changed the field names ( removed spaces ).also added "" instead of "none" and "\r\n" for the last one. |
|
|
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 outputhow can I make a fixed width text file if I can't truncate data ? |
|
|
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. |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2005-06-02 : 09:53:59
|
its now working, i think...bcp is hassle ! |
|
|
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. |
|
|
Previous Page&nsp;
Next Page
|