Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2012
    Posts
    32

    Unanswered: SQL Like,Contains

    my database entry is 62301
    there could be another entry that is 5602 (Varying length)

    I am trying to create a select query for '5462162301'

    How can I utilize like, contain, or in to create a query that will find entry 62301

    I can't use right(field, #) b/c the varying length and that there is a risk that the entries could be varied from left to right...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Make both attributes separate columns in your table instead of storing them in a combined form.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2012
    Posts
    32
    cant do that....its all part of the same entry

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is it only two parts? If so
    Code:
    select columns
    from table
    where column like '%62301'
    This will of course cause all sorts of table scans. If you expect to be searching on this value, or manipulating this value a lot, then you would probably be better off as Pat says to try to break the two apart.

  5. #5
    Join Date
    Aug 2012
    Posts
    32
    the issue is that I need this done by a column name, not the specified number '62301'

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ow. If the column name is dynamic, then there is not a lot you can do. Or really, there is too much you will have to do. You would have to query the system tables to find the full column name, then use that to build a dynamic sql statement. That would eb a pain to debug.

  7. #7
    Join Date
    Aug 2012
    Posts
    32
    I figured out how to concatenate '%' + column.name


    the query didnt return the correct entry though...

  8. #8
    Join Date
    Aug 2012
    Posts
    32
    I cant get the full column name, lol, its entered partially, and it varies in length

  9. #9
    Join Date
    Aug 2012
    Posts
    32
    i mean the full entry

  10. #10
    Join Date
    Aug 2012
    Posts
    32
    the actual data as opposed to the data that is entered is not in the system

  11. #11
    Join Date
    Aug 2012
    Posts
    32
    somehow I need to figure out a query that can do the following

    Select * from Field where [database.entry] is within [my search number]

  12. #12
    Join Date
    Aug 2012
    Posts
    32
    any ideas??

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This is what I was getting at earlier.
    Code:
    create table test1
    (col1 int,
    col123 varchar(20),
    col1234 varchar(20),
    col12345 varchar(20))
    
    insert into test1 values
    	(1, 'hello', 'hi', 'good morning'),
    	(2, 'good bye', 'bye', 'later')
    	
    -------------------------------------------------
    
    declare @partialColumn varchar(128)
    declare @fullColumn varchar(128)
    set @partialColumn ='1234'
    
    select @fullColumn = name
    from sys.columns
    where object_id('test1') = object_id
      and name like '%' + @partialColumn
      
    select @fullColumn
    
    declare @sql varchar(max)
    
    set @sql = 'select * from test1 where ' + @fullColumn + ' = ''hi'''
    
    exec (@sql)
    Like I say, it will be a pain to debug.

Posting Permissions

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