August 11, 2011 at 11:01 am
Hi
In oracle i could use the rpad or lpad functions to pad my variable,however, i do not believe these functions exsits in sql server. how can i do this.
Actually, i need my output to be fixed length
select
pjt_no, primary_street_addr, city,st, left( ' ' + cast( zip_code AS varchar(9))), 9 )
from
openquery(daps_linked, 'select * from app_property,a1_pipe_snapshot where application_id = dap_application_id ')
where pipe_stat_grp in ('IP','O','FIN REJ','WDRN','EXP')
Go
so, for example
pjt_no ----50 characters
primary_street_addr --- 100 characters
once i see one example i can code the rest
August 11, 2011 at 11:04 am
If you want fixed length use a fixed length datatype.
cast( zip_code AS char(9))
That will effectively right pad your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2011 at 12:13 pm
Hope this gives you a start:
CREATE TABLE #T(pjt_no VARCHAR(30),Street VARCHAR(100))
INSERT INTO #T
SELECT 'abcde','1 Main Street' UNION ALL
SELECT 'xyz','1 S.View Ave'
SELECT SUBSTRING(pjt_no + REPLICATE(' ',50) ,1,50) FROM #T
--To test the above SELECT
SELECT DATALENGTH(SUBSTRING(pjt_no + REPLICATE(' ',50) ,1,50)) FROM #T --This should return a value of 50 for each of my sample
rows.
And apply the REPLICATE and SUBSTRING functions to each field whose length you need to set to a fixed value.
Now this type of formatting is best done outside of SQL, that is by the application receiving the data.
August 12, 2011 at 11:23 am
To add a note on the current answers, REPLICATE() is a wonderful function. So is STUFF(), though STUFF() will actually replace an existing character, so be careful.
You can use them on the left side of the equation too, so long as you are putting your value into a CHAR() or VARCHAR() field. Use CHAR() if padding to the right. VARCHAR() trims on the right, but it doesn't trim on the left.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply