Post reply

Convert the string value to multiple rows in sql server

  • October 5, 2017 at 5:28 am

    #339797

    hi this is my requirement.

    ---Input
    declare @name varchar(10) = 'Server'

    select @name

    ---Output:
    S
    e
    r
    v
    e
    r

  • October 5, 2017 at 5:33 am

    #1962441

    Homework?  What have you tried?  You might want to have a look at this.

    John

  • October 5, 2017 at 7:05 am

    #1962458

    declare @name varchar(10) = 'Server';

    SELECT TOP (LEN(@name)) SUBSTRING(@name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 1)
    FROM sys.all_objects;

    This will do the job, but the question is, do you understand what it's doing?   Read up on the ROW_NUMBER() function, as well as the valid expressions you can use with the TOP operator.   These are things you can find online in the Microsoft documentation for SQL Server, and a quick Google search can probably get you to the exact page you need to look at for each of those items.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 5, 2017 at 7:28 am

    #1962469

    Thank you so much.

  • October 5, 2017 at 7:58 am

    #1962476

    kbhanu15 - Thursday, October 5, 2017 7:28 AM

    Thank you so much.

    Do you understand the solution proposed?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 5, 2017 at 8:03 am

    #1962480

    sgmunson - Thursday, October 5, 2017 7:05 AM

    declare @name varchar(10) = 'Server';

    SELECT TOP (LEN(@name)) SUBSTRING(@name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 1)
    FROM sys.all_objects;

    This will do the job, but the question is, do you understand what it's doing?   Read up on the ROW_NUMBER() function, as well as the valid expressions you can use with the TOP operator.   These are things you can find online in the Microsoft documentation for SQL Server, and a quick Google search can probably get you to the exact page you need to look at for each of those items.

    I disagree with using system tables as tally tables.  It could produce incorrect results if the user has permission to view fewer objects than there are bytes in the string.  And there's no need for such a nasty kludge anyway: why not just use an inline tally table or an actual tally table?

    I know MS did it in their code, but that's surely not a sufficient reason to copy it.  And at least MS knew their code had full access to all entries in the table, since it was running as sysadmin.  I know that's just my view, since people also copy MS's rather dopey index and other object naming conventions as well.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

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