Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

using select into and controlling varchar sizes Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2011 12:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 156, Visits: 681
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.
Post #1213494
Posted Tuesday, November 29, 2011 12:40 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1213498
Posted Tuesday, November 29, 2011 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 156, Visits: 681
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.
Post #1213506
Posted Tuesday, November 29, 2011 1:26 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1213527
Posted Tuesday, November 29, 2011 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1213534
Posted Tuesday, November 29, 2011 1:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 156, Visits: 681
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,

Post #1213542
Posted Tuesday, November 29, 2011 2:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1213549
Posted Tuesday, November 29, 2011 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1213550
Posted Tuesday, November 29, 2011 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 5,384, Visits: 7,458
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
Post #1213558
Posted Tuesday, November 29, 2011 2:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 156, Visits: 681
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.


Post #1213560
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse