+ Reply to Thread
Results 1 to 4 of 4

SQL Query requiring split

  1. SQL Query requiring split

    Hi,

    I am stuck on a query and was wondering if anyone can help.

    I have a table and within one of the fields i need to split it after
    each delimiter which is a /

    so the table name is Watch

    and it has the following 3 fields and values:


    watchID timeID description
    ----------- ------------
    -------------------
    1 w123 go/stop/wait
    2 w333 wait/pause/go
    3 w444 go/stop
    4 s456 wait/pause/stop/start/go


    what i would like to create another table with all 3 above fields plus
    another field to put the values in description when they are split.

    How to i create a query to turn the data into this as the output:


    watchID timeID description
    output
    ------------ ------------
    ----------------
    ----------
    1 w123 go/stop/wait go
    1 w123 go/stop/wait stop
    1 w123 go/stop/wait wait
    2 w333 wait/pause/go wait
    2 w333 wait/pause/go pause
    2 w333 wait/pause/go go
    3 w444 go/stop go
    3 w444 go/stop stop
    4 s456 wait/pause/stop/start/go wait
    4 s456 wait/pause/stop/start/go pause
    4 s456 wait/pause/stop/start/go stop
    4 s456 wait/pause/stop/start/go start
    4 s456 wait/pause/stop/start/go go


    I hope this makes sense.

    thanks.

  2. Re: SQL Query requiring split

    (woohoo30athotmaildotcom) writes:
    > I am stuck on a query and was wondering if anyone can help.
    >
    > I have a table and within one of the fields i need to split it after
    > each delimiter which is a /

    Have a look at http://www.sommarskog.se/arrays-in-s...tml#tablelists.

    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  3. Re: SQL Query requiring split

    On Apr 5, 5:30*pm, Erland Sommarskog wrote:
    > *(wooho...athotmaildotcom) writes:
    > > I am stuck on a query and was wondering if anyone can help.
    >
    > > I have a table and within one of the fields i need to split it after
    > > each delimiter which is a /
    >
    > Have a look athttp://www.sommarskog.se/arrays-in-sql-2005.html#tablelists..
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esq...atsommarskogdotse
    >
    > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
    > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


    thanks but is there a SQL script that can be written without CLR
    functions?

  4. Re: SQL Query requiring split

    On Apr 6, 9:42*am, wooho...athotmaildotcom wrote:
    > On Apr 5, 5:30*pm, Erland Sommarskog wrote:
    >
    > > *(wooho...athotmaildotcom) writes:
    > > > I am stuck on a query and was wondering if anyone can help.
    >
    > > > I have a table and within one of the fields i need to split it after
    > > > each delimiter which is a /
    >
    > > Have a look athttp://www.sommarskog.se/arrays-in-sql-2005.html#tablelists.
    >
    > > --
    > > Erland Sommarskog, SQL Server MVP, esq...atsommarskogdotse
    >
    > > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
    > > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    >
    > thanks but is there a SQL script that can be written without CLR
    > functions?


    I got it to work...I did use the function. Brilliant! Thanks so much
    Erland :)
+ Reply to Thread
« Previous Thread | Next Thread »