SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using select into and controlling varchar sizes


using select into and controlling varchar sizes

Author
Message
cafescott
cafescott
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 733
Hi,

I have a really long select-into statement that has a lot of calls to user-defined string functions. I would like to limit the size of the varchar strings in the resulting table. I can do it with what I regard as ugly code; and I'm looking for a better way.

Here's a function I wrote that pads strings on the left side:

CREATE FUNCTION [xyz].[fLeft_Pad] (@vPadChar char(1), @vStr varchar(255), @vLen int)
returns varchar (255)
as
BEGIN
declare @vOutput varchar(255)
set @vOutput = REPLICATE(@vPadChar, @vLen)
set @vOutput = RIGHT(@vOutput + LTrim(RTrim(@vStr)), @vLen)
return @vOutput
END



Here is a sample select-into statement:

select
xyz.fLeft_Pad('0', Str(123456789),9) as [Zip Code1],
Left(xyz.fLeft_Pad('0', Str(123456789),9),9) as [Zip Code2],
cast(xyz.fLeft_Pad('0', Str(123456789),9) as varchar(9)) as [Zip Code3],
'123456789' as [Zip Code4]
into
xyz.tblTest




In the result table (xyz.tblTest), [Zip Code1] is a varchar with length 255. The other three fields are length 9.

Meanwhile, in the interest of reducing clutter, I would like the fLeft_Pad function to work by itself. That is, without a Left or Cast function before it, I would like to call the function and have the result table create a field that is of type varchar(9).

I've tried changing the size of the varchar size in the return specification for the function. For example:

CREATE FUNCTION [xyz].[fLeft_Pad] (@vPadChar char(1), @vStr varchar(255), @vLen int)
returns varchar
as
...

However, calling this function always results in a varchar of size 1.

Does anyone have an idea? Thanks in advance.
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10096 Visits: 4894
Why are you using SELECT INTO as opposed to an INSERT INTO?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
cafescott
cafescott
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 733
I'm using select-into rather than insert-into because I don't have a good idea what the table structure is. The select statement is huge, and it is much easier to do a select-into.

thanks.
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10096 Visits: 4894
cafescott (11/29/2011)
I'm using select-into rather than insert-into because I don't have a good idea what the table structure is. The select statement is huge, and it is much easier to do a select-into.


I would explicitly create the table.

You should be able to easily identify what the structure is and you will have better control if you use an INSERT INTO.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25857 Visits: 17509
You are getting varchar(255) because that IS the datatype returned from your function. You could not use the function and do something like this.


select
right(replicate('0', 9) + '1234', 9) as [Zip Code1]
into
tblTest



_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
cafescott
cafescott
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 733
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,
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10096 Visits: 4894
You are not going to be able to return a variant data type.

If your RETURN VARCHAR(255) then that will be the data type for each column that you use the function on.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25857 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8551 Visits: 7660
The other option is to force the datatype on the select before it hits the select into.

IE: SELECT CONVERT( varchar(9), xyz.fLeft_Pad('0', Str(123456789),9))

At that point though you might as well define the table.

Out of curiousity, what are you going to do with hard tables in a SELECT INTO when it's ran concurrently? This technique is almost explicitly used for #tmp tables because of collision concerns.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
cafescott
cafescott
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 733
Thanks, Sean. I think dynamic sql would be a big headache. I'll just make do with the way it is.

Thanks Welsh Corgi, also.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search