Professional Web Applications Themes

Query- Count how many occurances of certain text in a column - Microsoft SQL / MS SQL Server

Hi everybody, First time I've posted in this group so go easy on me :-). Hopefully somebody can help. My website runs off an SQL database and I'm trying to write a query in Microsoft SQL Query yser. So, I've got a table called tbl_outgoing, and within it is a column called number. A typical field contains text such as below: <number>44773</number><number>44776</number><number>44779</number> There can be anything from 1 to 100+ <number>x</number> entries in a field. I need a query to count the amount of times <number> (or 'number'/2) occurs in the whole column. TIA, -- Chris Michael [url]www.INTOmobiles.com[/url] *** FREE ...

  1. #1

    Default Query- Count how many occurances of certain text in a column

    Hi everybody,

    First time I've posted in this group so go easy on me :-). Hopefully
    somebody can help. My website runs off an SQL database and I'm trying to
    write a query in Microsoft SQL Query yser.

    So, I've got a table called tbl_outgoing, and within it is a column called
    number. A typical field contains text such as below:
    <number>44773</number><number>44776</number><number>44779</number>

    There can be anything from 1 to 100+ <number>x</number> entries in a
    field. I need a query to count the amount of times <number> (or 'number'/2)
    occurs in the whole column.

    TIA,
    --
    Chris Michael
    [url]www.INTOmobiles.com[/url]
    *** FREE BIG BROTHER ALERTS ***
    Free ringtones/logos
    Free mobile alerts
    3 months free insurance


    Chris Michael Guest

  2. #2

    Default Re: Query- Count how many occurances of certain text in a column

    If the datatype of the column is VARCHAR or NVARCHAR,

    [url]http://www.aspfaq.com/2430[/url]

    If the datatype is TEXT, you might have to distribute it in chunks of 8000
    characters (since you can't do replace() on a text column, and you can't see
    charindex past 8000 chars), and then hope none of the chunks broke in the
    middle of the tag, e.g. <num|end of chunk one, beginning of chunk 2|ber>




    "Chris Michael" <chris.michaelREMOVEMEintomobiles.com> wrote in message
    news:bdsm9n$mb$1newsg3.svr.pol.co.uk...
    > Hi everybody,
    >
    > First time I've posted in this group so go easy on me :-). Hopefully
    > somebody can help. My website runs off an SQL database and I'm trying to
    > write a query in Microsoft SQL Query yser.
    >
    > So, I've got a table called tbl_outgoing, and within it is a column called
    > number. A typical field contains text such as below:
    > <number>44773</number><number>44776</number><number>44779</number>
    >
    > There can be anything from 1 to 100+ <number>x</number> entries in a
    > field. I need a query to count the amount of times <number> (or
    'number'/2)
    > occurs in the whole column.
    >
    > TIA,
    > --
    > Chris Michael
    > [url]www.INTOmobiles.com[/url]
    > *** FREE BIG BROTHER ALERTS ***
    > Free ringtones/logos
    > Free mobile alerts
    > 3 months free insurance
    >
    >

    Aaron Bertrand - MVP Guest

Similar Threads

  1. Count unique data in column
    By kev in forum MySQL
    Replies: 10
    Last Post: June 2nd, 08:51 PM
  2. Column count for list box won't change
    By almcnicoll in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: March 30th, 03:54 PM
  3. column count
    By Reggie in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: November 7th, 08:23 AM
  4. Datagrid Column Count HELP
    By Christopher Calhoun in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 29th, 04:54 PM
  5. Datagrid column count = 0??
    By Kris Rudin in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: November 11th, 09:20 AM

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