Professional Web Applications Themes

SQL SELECT Based on *PART* a text Field? - ASP

Hello, I'm trying to figure out some SQL here and am wondering this: Is it possible to select data based on a sub-string of a text field? Say I have these values in a field called "ROOMS": |1|2|5|45|36|27| And say the ROOMS field in any record will always be pipe-enclosed numeric values. Is there any way for me to select records based on all ROOMS fields that contain "|45|"? I have no idea what the proper (if possible) syntax would be, but here's my thinking: SELECT * FROM TableName WHERE the ROOMS field contains the string "|45|" Any help would ...

  1. #1

    Default SQL SELECT Based on *PART* a text Field?

    Hello,

    I'm trying to figure out some SQL here and am wondering this:

    Is it possible to select data based on a sub-string of a text field?

    Say I have these values in a field called "ROOMS": |1|2|5|45|36|27|
    And say the ROOMS field in any record will always be pipe-enclosed numeric
    values.

    Is there any way for me to select records based on all ROOMS fields that
    contain "|45|"?

    I have no idea what the proper (if possible) syntax would be, but here's my
    thinking:

    SELECT * FROM TableName
    WHERE the ROOMS field contains the string "|45|"

    Any help would be greatly appreciated!

    --
    /bw

    ----------------------------------------------------------------------
    please reply to the group for everyone's benefit
    if you must reply via email, just remove the NOSPAM from the address



    wetchman Guest

  2. #2

    Default Re: SQL SELECT Based on *PART* a text Field?

    This is one way:
    SELECT [TheColumns] FROM [TableName] WHERE [ROOMS] LIKE '%|45|%'

    Or move your rooms to their own table so you aren't storing data within data
    like this.

    Ray at work

    "wetchman" <wetchmanNOSPAMhotmail.com> wrote in message
    news:vbo9b.1$KH2.0fe01.atl2.webusenet.com...
    > Hello,
    >
    > I'm trying to figure out some SQL here and am wondering this:
    >
    > Is it possible to select data based on a sub-string of a text field?
    >
    > Say I have these values in a field called "ROOMS": |1|2|5|45|36|27|
    > And say the ROOMS field in any record will always be pipe-enclosed numeric
    > values.
    >
    > Is there any way for me to select records based on all ROOMS fields that
    > contain "|45|"?
    >
    > I have no idea what the proper (if possible) syntax would be, but here's
    my
    > thinking:
    >
    > SELECT * FROM TableName
    > WHERE the ROOMS field contains the string "|45|"
    >

    Ray at Guest

  3. #3

    Default Re: SQL SELECT Based on *PART* a text Field?

    Ray,

    Thanks - and I have thought about storing the data in another table, but
    that would greatly complicate my already complex setup, at least from my
    limited knowledge of SQL and designing a good DB. But then, maybe that's why
    I'm seeing my issues as complex and overlooking simple things like a "LIKE"
    clause - because I haven't designed a very good db...

    Thanks again,

    /bw


    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:%23vERA$7eDHA.1088TK2MSFTNGP10.phx.gbl...
    > This is one way:
    > SELECT [TheColumns] FROM [TableName] WHERE [ROOMS] LIKE '%|45|%'
    >
    > Or move your rooms to their own table so you aren't storing data within
    data
    > like this.
    >
    > Ray at work
    >
    > "wetchman" <wetchmanNOSPAMhotmail.com> wrote in message
    > news:vbo9b.1$KH2.0fe01.atl2.webusenet.com...
    > > Hello,
    > >
    > > I'm trying to figure out some SQL here and am wondering this:
    > >
    > > Is it possible to select data based on a sub-string of a text field?
    > >
    > > Say I have these values in a field called "ROOMS": |1|2|5|45|36|27|
    > > And say the ROOMS field in any record will always be pipe-enclosed
    numeric
    > > values.
    > >
    > > Is there any way for me to select records based on all ROOMS fields that
    > > contain "|45|"?
    > >
    > > I have no idea what the proper (if possible) syntax would be, but here's
    > my
    > > thinking:
    > >
    > > SELECT * FROM TableName
    > > WHERE the ROOMS field contains the string "|45|"
    > >
    >
    >


    wetchman Guest

  4. #4

    Default Re: SQL SELECT Based on *PART* a text Field?

    I have a database here myself with the same exact thing. {:] But I made it
    before I got yelled at for it. One of these days I'll redo it and the
    system that uses it...

    Ray at work

    "wetchman" <wetchmanNOSPAMhotmail.com> wrote in message
    news:4Bo9b.85$KH2.41fe01.atl2.webusenet.com...
    > Ray,
    >
    > Thanks - and I have thought about storing the data in another table, but
    > that would greatly complicate my already complex setup, at least from my
    > limited knowledge of SQL and designing a good DB. But then, maybe that's
    why
    > I'm seeing my issues as complex and overlooking simple things like a
    "LIKE"
    > clause - because I haven't designed a very good db...
    >
    > Thanks again,
    >
    > /bw

    Ray at Guest

Similar Threads

  1. only show part of a dynamic text field
    By ccarterca in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: October 28th, 03:25 PM
  2. Select from a text field!
    By Alex M. K. in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 20th, 05:19 PM
  3. Replies: 1
    Last Post: September 16th, 05:00 AM
  4. Changing button's URLs based upon text field
    By cooped webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 21st, 06:03 PM
  5. Updating part of the text in an NTEXT field
    By Rocky in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 10th, 02:29 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