Professional Web Applications Themes

How do you SELECT a list of words with commas in SQL from a column? - ASP Database

I.e., here are some rows of data: word1 word2, word3, word4 word5, word6 ....etc and I would like a single column like this: word1 word2 word3 word4 word5 word6 preferably I would like DISTINCT words (no repetitions). Any ideas? Thanks, Mike...

  1. #1

    Default How do you SELECT a list of words with commas in SQL from a column?

    I.e., here are some rows of data:

    word1
    word2, word3, word4
    word5, word6
    ....etc


    and I would like a single column like this:
    word1
    word2
    word3
    word4
    word5
    word6

    preferably I would like DISTINCT words (no repetitions). Any ideas?

    Thanks,
    Mike
    Mike Guest

  2. #2

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    I'm not sure how you would do this with a SQL statement, but how about
    pulling out the data and splitting the string in code. Once the string is
    split and all the data is in an array, you could manipulate it as needed.


    --
    ~Brad Kingsley
    Microsoft MVP - ASP/.Net
    Windows 2000 MCSE

    http://www.orcsweb.com/
    Powerful Web Hosting Solutions
    #1 in Service and Support






    "Mike" <com> wrote in message
    news:google.com... 


    Brad Guest

  3. #3

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    Mike wrote: 

    Extremely bad database design. But you know that now, don't you?
     


    It depends on your database type and version (please: always specify the
    database type and version. Even if you've just posted another thread in
    which you specified a database. There is nothing that says you could not be
    using two different databases, is there? ).

    If Access, you are SOL. A VBA function could be written to insert the
    discrete pieces of data into a work table, but this function could not be
    called from asp. So you will need to resort to inefficient vbscript code to
    do it (unless you create a VB dll)

    If SQL 2000+, you can create a stored procedure to do this.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  4. #4

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<phx.gbl>... 
    >
    > Extremely bad database design. But you know that now, don't you?[/ref]

    Yes this group has taught me big words like normalization and
    accesss. However I created this data last year before my eyes
    were opened...
     
    >
    >
    > It depends on your database type and version (please: always specify the
    > database type and version. Even if you've just posted another thread in
    > which you specified a database. There is nothing that says you could not be
    > using two different databases, is there? ).
    >
    > If Access, you are SOL. A VBA function could be written to insert the
    > discrete pieces of data into a work table, but this function could not be
    > called from asp. So you will need to resort to inefficient vbscript code to
    > do it (unless you create a VB dll)
    >
    > If SQL 2000+, you can create a stored procedure to do this.[/ref]

    Ah, a week ago I would have sighed, but now I have sql server! Please
    spill the beans Bob!! (the details of inputting the sp into query
    yzer would help too)
    Mike Guest

  5. #5

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    Mike wrote: [/ref][/ref]
     
    >> If SQL 2000+, you can create a stored procedure to do this.[/ref]
    >
    > Ah, a week ago I would have sighed, but now I have sql server! Please
    > spill the beans Bob!! (the details of inputting the sp into query
    > yzer would help too)[/ref]

    Isn't now the time to correct your database design ... ?

    However ...
    Here is a solution suggested by the examples here:
    http://www.bizdatasolutions.com/tsql/sqlarrays.asp. Now that I look at this,
    I see that it can be adapted for use in Access as well by replacing
    CHARINDEX with InStr, and SUBSTRING with Mid (and correcting the syntax). I
    would still use a stored procedure, for which I've shown the syntax below:

    SELECT TOP 100 IDENTITY(INT) As Number INTO Numbers
    FROM sysobjects s1
    CROSS JOIN sysobjects s2

    go
    create table PoorDesign (Words varchar(8000))
    INSERT INTO PoorDesign
    SELECT 'word1, word4'
    UNION SELECT 'word2, word3, word4'
    UNION SELECT 'word1, word5, word6'
    go

    CREATE PROCEDURE ExtractWords AS
    SELECT DISTINCT LTRIM(SUBSTRING(',' + Words + ',', Number + 1,
    CHARINDEX(',', ',' + Words + ',', Number + 1)
    - Number - 1)) AS [item]
    FROM PoorDesign CROSS JOIN Numbers
    WHERE SUBSTRING(',' + Words + ',', number, 1) = ','
    AND Number < LEN(',' + Words + ',')

    In ASP, you would run the procedure like this:

    'create and open a connection - cn
    set rs=createobject("adodb.recordset")
    cn.ExtractWords rs
    If not rs.eof then
    etc.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  6. #6

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<#phx.gbl>... [/ref]

    > >
    > > Ah, a week ago I would have sighed, but now I have sql server! Please
    > > spill the beans Bob!! (the details of inputting the sp into query
    > > yzer would help too)[/ref]
    >
    > Isn't now the time to correct your database design ... ?
    >
    > However ...
    > Here is a solution suggested by the examples here:
    > http://www.bizdatasolutions.com/tsql/sqlarrays.asp. Now that I look at this,
    > I see that it can be adapted for use in Access as well by replacing
    > CHARINDEX with InStr, and SUBSTRING with Mid (and correcting the syntax). I
    > would still use a stored procedure, for which I've shown the syntax below:
    >
    > SELECT TOP 100 IDENTITY(INT) As Number INTO Numbers
    > FROM sysobjects s1
    > CROSS JOIN sysobjects s2
    >
    > go
    > create table PoorDesign (Words varchar(8000))
    > INSERT INTO PoorDesign
    > SELECT 'word1, word4'
    > UNION SELECT 'word2, word3, word4'
    > UNION SELECT 'word1, word5, word6'
    > go
    >
    > CREATE PROCEDURE ExtractWords AS
    > SELECT DISTINCT LTRIM(SUBSTRING(',' + Words + ',', Number + 1,
    > CHARINDEX(',', ',' + Words + ',', Number + 1)
    > - Number - 1)) AS [item]
    > FROM PoorDesign CROSS JOIN Numbers
    > WHERE SUBSTRING(',' + Words + ',', number, 1) = ','
    > AND Number < LEN(',' + Words + ',')
    >
    > In ASP, you would run the procedure like this:
    >
    > 'create and open a connection - cn
    > set rs=createobject("adodb.recordset")
    > cn.ExtractWords rs
    > If not rs.eof then
    > etc.
    >
    > Bob Barrows[/ref]

    Thanks for this bob!

    I've got another problem. actually, its related. Say I have a table
    of sentences, each one different, like this:

    Table structure
    id
    sentence

    sentence example:

    The [Animal] jumped over the [FurnitureObject] while the its
    [Association], the [Animal], slowly crawled along the [Surface].

    The words in brackets are values from the table named in the brackets.

    Basically, I want to limit what the values can be to the contents of
    those tables, and I want to be able to change the contents of the
    tables so that all the sentences dont have to be individually updated.
    For example, if the Animal table had an entry "Cat" and I later
    decided that all the sentences would sound better with the word
    "Tomcat", I would simply change the value in the Animal table and all
    the sentences would be corrected when they are regenerated from the
    database.

    I was thinking of making a database with a memo field to contain the
    sentences, then within the field store the sentence like this:

    The [Animal:23] jumped over the [FurnitureObject:12] while the its
    [Association:5], the [Animal:22], slowly crawled along the
    [Surface:12].

    Then in I guess ASP, I would p the sentence and lookup the value
    from the specified table. Of course bells are going off in my head
    now that I have been hanging around this group telling me that this is
    completely the wrong way to do it... so...

    How do you store this structure "correctly" when each sentence can
    have a different length, words-from-tables positioning within the
    sentence, wording, number of values from the tables, and differing
    numbers of tables involved (in other words, for an arbitrary sentence
    drawing a few words from tables).

    Thanks,

    Mike
    Mike Guest

  7. #7

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    Mike wrote: 
    I need to think about this. I'll get back to you.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  8. #8

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<#phx.gbl>...

    (in other words, for an arbitrary sentence 
    > I need to think about this. I'll get back to you.[/ref]

    Hi Bob,

    How about this??

    SentenceTable:
    id (autonumber)
    sentence (string)

    SentenceIndexingTable:
    id (autonumber)
    sentenceId (number)
    WhichTableId (number)
    valueId (number)
    positionInSentence (number)

    WhichTable
    id (autonumber)
    tableName (string)

    (I'm assuming that the tables in a database don't have an index to
    identify them by already. At least in Access they don't seem to have
    one I can use. Do they have one in SQL Server?)

    The rest of the tables are like this:

    Animal
    id (autonumber)
    value (string)

    Surface
    id (autonumber)
    value (string)

    How about it?

    -Mike
    Mike Guest

  9. #9

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    It's getting there. At least you've gotten away from storing all the words
    in a single field.

    I do not think you need all the separate category tables and the WhichTable
    table. A single table should do. Instead of
    Animal
    id (autonumber)
    value (string)

    You would have:

    KeyWords
    KeywordID identity
    Category (Animal, Surface, etc - used to populate dropdown lists)
    value

    I was thinking more in terms of:

    SentenceFrags
    SentenceID identity
    FragPosition int
    FragText varchar (null if reference to keywords table)
    KeywordCategory
    varchar (null if not a reference to the keywords table)
    KeywordID int (null if not a reference to the keywords table)


    Using your example, The Keywords table would contain

    1 Animal cat
    33 FurnitureObject table
    43 Association brother
    55 Surface floor


    SentenceFrags would contain:

    1 The 1
    1 2 Animal
    1
    1 jumped over the 3
    1 4 FurnitureObject
    33
    1 while the its 5
    1 6 Association
    43
    1 , the 7
    1 8 Animal
    1
    1 , slowly crawled along the 9
    1 10 Surface
    55
    1 . 11

    Make sure the FragText contains leading spaces where necessary.
    In SQL Server, the query to get results from this would look like:

    CREATE PROCEDURE GetSentence (
    SentenceID int) AS
    SET NOCOUNT ON
    SELECT
    COALESCE(FragText,k.value) FragText,
    FROM
    SentenceFrags f LEFT JOIN Keywords k
    ON f.KeywordID = k.KeywordID
    WHERE SentenceID = SentenceID
    ORDER BY FragPosition

    Run this script in Query yzer, then test it like this:
    EXEC GetSentence 1

    In ASP, you would call this procedure like this:

    Dim sentenceid, sentence
    sentenceid = <request variable containing the value 1>
    Set rs=server.createobject("adodb.recordset")
    conn.GetSentence sentenceid,rs
    if not rs.eof then
    sentence = rs.GetString(2,,"","")
    else
    sentence = "no sentence returned"
    end if
    rs.close:set rs=nothing
    conn.close: set conn=nothing


    And no, indexes do not exist by default in practically any rdbms. You need
    to create them yourself.
    Mike wrote: 
    >> I need to think about this. I'll get back to you.[/ref]
    >
    > Hi Bob,
    >
    > How about this??
    >
    > SentenceTable:
    > id (autonumber)
    > sentence (string)
    >
    > SentenceIndexingTable:
    > id (autonumber)
    > sentenceId (number)
    > WhichTableId (number)
    > valueId (number)
    > positionInSentence (number)
    >
    > WhichTable
    > id (autonumber)
    > tableName (string)
    >
    > (I'm assuming that the tables in a database don't have an index to
    > identify them by already. At least in Access they don't seem to have
    > one I can use. Do they have one in SQL Server?)
    >
    > The rest of the tables are like this:
    >
    > Animal
    > id (autonumber)
    > value (string)
    >
    > Surface
    > id (autonumber)
    > value (string)
    >
    > How about it?
    >
    > -Mike[/ref]

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  10. #10

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    Aargh. the line breaks spoiled my example. let's use insert statements
    instead to hopefully make it a little clearer:


    insert into SentenceFrags (
    SentenceID,
    FragPosition,
    FragText,
    KeywordCategory,
    KeywordID )
    SELECT
    1, 'The', 1,Null,Null
    UNION SELECT
    1,Null, 2, 'Animal',1
    UNION SELECT
    1, ' jumped over the' 3,Null,Null
    UNION SELECT
    1, Null, 4, 'FurnitureObject',33
    UNION SELECT
    1, ' while the its', 5,Null,Null
    UNION SELECT
    1, Null, 6, 'Association'43
    UNION SELECT
    1 , 'the', 7,Null,Null
    UNION SELECT
    1, Null, 8, 'Animal',1
    UNION SELECT
    1 , slowly crawled along the 9,Null,Null
    UNION SELECT
    1, Null, 10, 'Surface'55
    UNION SELECT
    1 '.',11,Null,Null



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  11. #11

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    I'm going to put this in and try it.

    Another issue I'm having is how does the user enter the data.

    I was thinking that the user would type into a textarea like this:

    The quick brown [animal] jumped over the [object] ...

    Then presses 'submit', ASP generates a webpage by parsing the string
    and inserting dropdowns at each table name in brackets. Then the user
    goes back and selects the values from the dropdowns to complete the
    sentence.

    Is that how you would do it?

    -Mike
    Mike Guest

  12. #12

    Default Re: How do you SELECT a list of words with commas in SQL from a column?

    Mike wrote: 
    It's hard to comment without knowing the business purpose, but: probably
    not, unless you can really trust your users not to make mistakes and use
    brackets where they're not supposed to. I would probably use a series of
    dynamic textboxes and dropdowns to build each sentence fragment, using a
    read-only textarea to display the final result before submitting.

    The page would load with something like this:


    Fragment Free form entry keyword enty (two dropdowns)
    1 <textbox> <category><associated keywords>
    <button to refresh textarea><button for adding new fragment>
    <textarea for displaying result of concatenating the fragments>
    <submit button>

    Clicking the <button for adding new fragment> would cause this to occur:

    Fragment Free form entry keyword enty (two dropdowns)
    1 <textbox> <category><associated keywords>
    Fragment Free form entry keyword enty (two dropdowns)
    2 <textbox> <category><associated keywords>
    <button to refresh textarea><<button for adding new fragment>
    <textarea for displaying result of concatenating the fragments>
    <submit button>

    Selecting a keyword in a row would clear the free-form textbox in that row
    .... and vice versa. Or maybe you would use a radio button in each row to
    toggle between free-form or keyword entry ...

    But I work solely in an intranet. These techniques would probably not be
    usable in an internet environment.You could probably do the above with
    multiple trips to the server for adding each fragment

    For details about the client-side code (if you choose that route), see one
    of the .scripting newsgroups (preferably the jscript group), or one of the
    groups with "dhtml" in its name.

    And now, I've got to get back to my own job :-)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

Similar Threads

  1. Converting a 3 column pdf address list to a one column MS word doc
    By Jay_Jump@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: February 9th, 06:46 PM
  2. List words in fulltext index
    By Ruben van Engelenburg in forum MySQL
    Replies: 0
    Last Post: July 27th, 01:06 PM
  3. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  4. Break paragraph into list of words / keyword detection
    By yvan@ideasdesign.com in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 20th, 02:31 PM
  5. select from drop list to fill table column with text -- HOW ?
    By Richard Galli in forum PHP Development
    Replies: 0
    Last Post: June 25th, 08:18 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