cafescott (11/29/2011)
Welsh Corgi, thanks for the suggestion. I am reluctant to change to the insert-into method because I think it will take longer overall.Sean, thanks for the reply. I already knew what you were saying. I'm really interested in knowing whether the function I wrote can return a string that is sized depending on what the input is. If not, then I'll live with ugly code.
Ideally, the function would work like this:
select
xyz.fLeft_Pad('0', Str(123456789), 9) as [Zip Code1],
xyz.fLeft_Pad(' ', 'foobar', 6) as [foobar]
...with the function returninig a varchar(9) for the first one and varchar(6) for the second.
Thanks,
Well a function can't return dynamic datatypes, that is the point is to return a defined datatype. You would have to do some dynamic sql to pull this off. That off course eliminates a function but you could probably do it in a stored proc.
_______________________________________________________________
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/