Professional Web Applications Themes

split the string by using the cursor - Microsoft SQL / MS SQL Server

hi, i need one help regarding the string comparasion using Cursors. the string contains "a,b,c,d",by using this string i want individual string to display basing on the comma operator. i have to display the result in this way a b c d how can i split the string by using cursors. thanks in advance. Naren...

  1. #1

    Default split the string by using the cursor

    hi,
    i need one help regarding the string comparasion using
    Cursors.
    the string contains "a,b,c,d",by using this string i want
    individual string to display basing on the comma operator.
    i have to display the result in this way
    a
    b
    c
    d
    how can i split the string by using cursors.
    thanks in advance.
    Naren

    Naren Guest

  2. #2

    Default Re: split the string by using the cursor

    Hi Naren,

    You can find the inforamtion you want in this article:
    http://www.algonet.se/~sommar/arrays-in-sql.html

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Naren" <com> wrote in message
    news:093c01c35ccb$0c11de80$gbl... 


    Jacco Guest

  3. #3

    Default Re: split the string by using the cursor

    Naren
    Steave Kass has written this procedure.I am sure you wil find it very
    helpful
    /*
    A query to turn a list into a result set containing
    the list items, one per row.

    Steve Kass, Drew University
    */

    --A table of integers is needed
    create table Seq (
    Nbr int not null
    )

    insert into Seq
    select top 4001 0
    from Northwind..[Order Details]
    cross join (select 1 as n union all select 2) X

    declare i int
    set i = -1
    update Seq
    set i = Nbr = i + 1

    alter table Seq add constraint pk_Seq primary key (Nbr)
    --table Seq created
    go

    create procedure PList (
    List varchar(8000)
    ) as

    --This makes things more readable. The list is easier
    --to process if it begins and ends with a single comma
    --As it turns out also, list items cannot
    --have leading or trailing spaces (here any leading spaces
    --in the first item or trailing spaces in the last are
    --eliminated)
    set List = replace(rtrim(','+ltrim(List))+',', ',,', ',')
    --The items are extracted by selecting those substrings of
    --the list that begin immediately after a comma and end
    --immediately before the next comma, then trimming spaces on
    --both sides.
    select distinct ltrim(rtrim(
    substring(List,
    commaPos+1,
    charindex(',', List, commaPos+1) - (commaPos+1))))
    as Item from (

    --This query returns a table containing one column, commaPos,
    --of integers, the positions of each comma in List, except the last
    select Nbr as commaPos from Seq
    where substring(List,Nbr,1) = ','
    and Nbr < len(List)
    ) N
    go

    --examples
    declare x varchar(4000), time datetime
    set x = te('foo,bar,', 4000/8 - 1) + 'ab'
    exec Plist x

    declare x varchar(4000)
    set x = '1,2,3'
    --set x = 'the fox jumped on the rabbit'
    declare s varchar(100)
    set s = replace(x,' ',',')
    exec Plist s
    --Note, if a list contains a non-comma delimiter, and contains no
    --commas within items, this replacement allows the function to
    --handle it. If a comma appears in an item, but some other non-
    --delimiter is absent from the list, a three-step replacement can
    --be made:
    -- replace all commas with new character not in list
    -- replace all delimiters with comma
    -- Use (select replace(Item,<new>,<comma>) from ListTable(List)) LT
    -- where the list table is used.
    go

    --Since this is a repro script, delete everything!
    --Keep them around if they are helpful, though.
    DROP procedure PList
    DROP TABLE Seq





    "Naren" <com> wrote in message
    news:093c01c35ccb$0c11de80$gbl... 


    Uri Guest

  4. #4

    Default Re: split the string by using the cursor

    An alternative would be using Rac. For this one specific issue, please take
    a look at http://rac4sql.net/onlinehelp.asp?topic=255

    Also, you might be interested to check out the "Working with Character
    Strings".

    Regards,
    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Naren" <com> wrote in message
    news:093c01c35ccb$0c11de80$gbl... 


    oj Guest

Similar Threads

  1. Split string?
    By Steve Grosz in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 13th, 10:21 PM
  2. How to split a special string...
    By Reply-Via-Newsgroup in forum PHP Development
    Replies: 4
    Last Post: February 3rd, 10:56 AM
  3. new warning in 1.8 from String#split
    By Phil Tomson in forum Ruby
    Replies: 0
    Last Post: October 11th, 06:14 PM
  4. split a tab delimited string
    By Ajit in forum ASP.NET General
    Replies: 6
    Last Post: July 29th, 03:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139