using select into and controlling varchar sizes

  • 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.

  • 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/

  • 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.

  • 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/

  • 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 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/

  • 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,

  • 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/

  • 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/

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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.

  • I agree that at this point just creating a table is a better approach but I just had to try such a thing with dynamic sql.

    create procedure [fLeft_Pad] (@vPadChar char(1), @vStr varchar(255), @vLen int)

    as

    BEGIN

    declare @sql nvarchar(200)

    select @sql = 'select cast(right(REPLICATE(''' + @vPadChar + ''', ' + cast(@vLen as varchar(5)) + ') + ''' + @vStr + ''', ' + cast(@vLen as varchar(5)) + ') as varchar(' + cast(@vLen as varchar(5)) + ')) as Result'

    --select @sql

    execute sp_executesql @sql

    END

    go

    exec fLeft_Pad '0', '1234', 9

    _______________________________________________________________

    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/

  • 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.

    hmm...Not sure I understand the question, Evil Kraig. I'm creating a table that is going to be used to generate output to a client who is expecting a flat file with specific column sizes for each field. Since there are hundreds of fields involved I think it will develop faster with select-into. Also, at the top of the routine I just delete the last table if it exists.

    thanks,

  • cafescott (11/29/2011)


    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.

    hmm...Not sure I understand the question, Evil Kraig. I'm creating a table that is going to be used to generate output to a client who is expecting a flat file with specific column sizes for each field. Since there are hundreds of fields involved I think it will develop faster with select-into. Also, at the top of the routine I just delete the last table if it exists.

    thanks,

    Allow me to clarify.

    User 1 begins process running script with a persisted table.

    User 2 begins process 10 seconds later, either blowing up user 1, failing himself, or getting User 1 and User 2 bad data. Since you delete at the beginning of the proc it's going to depend on who's got what locked at what point.

    Insert Into or Select Into doesn't matter at that point, you can have discontiguous information in the same location depending on user inputs. Select into just also requires the table to be non-existant too.

    This comment: "generate output to a client who is expecting a flat file with specific column sizes for each field" implies to me that we're discussing the wrong tool altogether. I would move this process to SSIS, define the flatfile output you're requiring, pull your query as a data source and do any necessary translations there. What is the current mechanism you're intending to output to flatfile with? I assume BCP?


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • IMHO, faster does not necessarily mean better.

    Are you going to define Indexes on the Table?

    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/

  • I find SELECT INTO as a very handy dandy tool to create a table quickly. If I'm a bit queasy about letting a big DELETE or UPDATE query loose on a table I'll do a quick 'SELECT * INTO Table_Safe FROM Table' as a quick and dirty backup of the table before letting the query rip at the real table.

    I also use it extensively for temp tables. That way I don't have to know which columns are DEC(15, 2) and which are DEC(15, 4) and so forth.

    If there can be any sort of contention, however, it's not a good practice.

    Todd Fifield

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply