Post reply

How to find a number in a space separated string?

  • June 22, 2004 at 4:01 am

    #152194

    I have a table with a varchar field (called nums)

    containing a number, or numbers separated by a space character.

    Example

    ID nums

    1 1 2

    2 5

    3 10

    4 12

    5 14 15

    6 101

    7 115

    8 141

    9 151

    Look at ID 1 who has the numbers 1 and 2 separated by a space,

    and at ID 5 who has the numbers 15 and 15 separated by a space.

    I want to search the record where nums contains a certain number.

    EG search for 10 giving recordID 3

    or search for 15 giving recordID 5

    or search for 2 giving recordID 1

    I think this is possible with a smart LIKE search condition,

    but I can't find out how.

    Is this possible and what would be the solution?

  • June 22, 2004 at 4:27 am

    #511372

    Ignore this post, having a bad day!

  • June 22, 2004 at 4:30 am

    #511373

    Surely the correct solution is to have a column for each number?

    Using LIKE will be tricky as using the clause WHERE nums LIKE '%10%' will returns rows 3 and 6.

    How about seperating the numbers with alphanumeric characters?

    Something like this may work:

    SELECT *

    FROM table

    WHERE nums LIKE '%[A-Z]' + @InputVariable + '[A-Z]&'

    I still think individual columns is the best way to go.

  • June 22, 2004 at 4:41 am

    #511377

    A column for each number would be the best solution, but it's another program (a CMS) that is filling the table, and it's not possible to change this.

    I found this solution:

    SELECT *

    FROM Table

    WHERE (nums LIKE '112') OR

    (nums LIKE '%[ ]' + '112') OR

    (nums LIKE '112' + '[ ]%') OR

    (nums LIKE '%[ ]' + '112' + '[ ]%')

    But it is not a nice solution, there must be a better way

  • June 22, 2004 at 6:26 am

    #511395

    declare @wantednum varchar(10)

    set @wantednum = '10'

    select ID

    from

    where charindex(' ' + @wantednum + ' ',' ' + nums + ' ') > 0

    Note that this will read every row in the table (either table or index scan).

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 22, 2004 at 2:06 pm

    #511514

    Thank you David !!!

    This is exactly wat I want, it works fine.

    I used your code in the Query Analyser and it workes fine.

    But when I use it in the Enterprise Manager I get no result,

    only a Message that one row is affected.

    When I use it this way:

    select ID

    from

    where charindex(' ' + '10'+ ' ', ' ' + nums + ' ') > 0

    I get the correct result.

    Is there a way to use it with a parameter in stead of a hard coded '10'?

    I did declare Properties - Parameters - Prefix as "?"

    And tried:

    select ID

    from

    where charindex(' ' + ?+ ' ', ' ' + nums + ' ') > 0

    But then I get the error message:

    The parameter can not be part of a function argument.

    Sorry that I ask you such a question, I am a kind of Newbie on MS SQL.

    And I need a SQL command with a parameter because I use it in a ASP.Net application.

  • June 23, 2004 at 12:56 am

    #511553

    Yes My Dear While visiting this I found out ur query first and go through that.

    I am sure u can declare a viable like before select statement or you can pass the parameter through ur SP.

    Declare @Num Int,

     Select @Num = 10

    select ID

    from

    where charindex(' ' + @Num+ ' ', ' ' + nums + ' ') > 0

     

    It will work ...

     

    Regards

    Deepak

     

  • June 23, 2004 at 2:44 am

    #511579

    Why are you running it in EM?

    EM will not like queries like this as it cannot substitue the parameter.

    If you are using this in .NET then you can either create a string containing the query with the value instead of ? or (the preferred method) as per Deepak, create a procedure.

    You can pass either to ADO ASP .NET

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 23, 2004 at 4:46 am

    #511591

    David (and Deepak) thank you for your explanation.

    You asked: Why in EM?

    That's because I try the SQL before giving it to a person who

    uses it in a asp.net application.

    As I don't know how to use parameters in the Query Analyser

    (is this possible anyway), I try SQL with params in EM.

    But when params are not possible I'll use the string method.

    (stored procedures are not possible because the CMS is not ours)

  • June 23, 2004 at 6:26 am

    #511605

    You could also use:

    SELECT *

    FROM table

    WHERE ' '+nums+' ' LIKE ' ' + @InputVariable + ' '

    so the ' ' (space, NOT empty string) will make you find also the numer 10 in '10 12'... (beginning or end of the nums column)

    HTH

    Gigi

  • June 28, 2004 at 3:51 am

    #512124

    Luigi,

    I tried your solution, but it didn't work.

    I think the idee is good, but the %-signs are missing.

    I tried:

    SELECT *

    FROM table

    WHERE ' '+nums+'% ' LIKE ' ' + @InputVariable + ' %'

    This is what you ment?

  • June 28, 2004 at 6:37 am

    #512143

    Hi Henk, sorry for the mistake, sometimes my finger can't follow my mind .

    I mean:

    WHERE ' '+nume+' ' LIKE '% ' + @InputVariable + ' %'

    the trick is to put spaces before and after both nume and inputvariable, so you always find the number (if exist) even at begin or end of the string, else you won't find the number at begin (or end) of the column. If you omit the spaces searching with '15' wil find also '150' or '1015'. You can use the space or any other delimiter you choose, as long as it is the same that is used between numbers in your column.

    Hi

    Luigi

     

  • June 28, 2004 at 7:02 am

    #512158

    Thanks,

    I'll use your (2nd) solution.

    Henk

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply