Post reply

HELP! - Search Records By Using a Delimited String

  • November 27, 2007 at 8:54 am

    #184114

    Hi Guys

    Just a small problem, but not having much luck. I need a stored proceedure which will search a table containing the following fields using a string supplied by asp.

    String

    LU1,LU2,LU3,LU4,LU5,SG1,WD1 - the string can be any length (well within reason anyway)

    Table (name: traderspostcodes)

    PrimerID (int) - example: '12'

    Postcode (navarchar) - example 'LU1 2CD'

    TraderID (int) - example: '234'

    The "Postcode" field has the full postcode as a value, where as the string only contains the first 3 digits of the postcode.

    What I need is to know the number of records which match any of the variables in the supplied string.

    Kind regards

    Steve

  • November 27, 2007 at 9:04 am

    #754608

    Hi Steve,

    My aproach would be something like,

    Stored Proc that accepts the comma delimited string as input and returns an int.

    In the stored proc unpack the string into a temp table or table variable , There are some good posts

    on this site how to do it.

    Join this to traderspostcodes on LEFT(Postcode ,3) AND do a COUNT(*).

    Allen

  • November 27, 2007 at 9:07 am

    #754613

    Hi Thanks for posting

    Can you provide me with an example. I'm not being lazy, it's just I have never had to do this before so any further help would be appriecated.

    Steve

  • November 27, 2007 at 9:17 am

    #754619

    Something like (Air code)

    CREATE PROCEDURE rpCountThePostcodes @InputString VARCHAR(255), @RowCount INT OUTPUT

    AS

    BEGIN

    CREATE TABLE #Postcode

    (Postcode CHAR(3))

    /* get some code to parse the string into table above */

    SELECT @RowCount = COUNT(*) FROM #Postcode p INNER JOIN traderspostcodes t on p.Postcode = LEFT(t.Postcode ,3)

    END

  • November 27, 2007 at 9:21 am

    #754622

    Hi

    Thanks for that - do understand it apart from the "t" and "p" ???

    Steve

  • November 27, 2007 at 9:24 am

    #754624

    The t and p are table aliases, you put them after the table name in the FROM clause and can then use them to refer to that table elswhere in the SELECT and other DML statements.

  • November 27, 2007 at 9:28 am

    #754626

    Hiya

    Thanks for that - should make life alot easier. Once thank you for posting, really appriecated (SQL not my thing, networks are, but having to cover for someone else today)

    Kind regards

    Steve

  • November 27, 2007 at 9:36 am

    #754631

    HTH - I know where to come looking when I my ADSL starts playing up 🙂

  • November 27, 2007 at 9:44 am

    #754640

    🙂 No worries

Viewing 9 posts - 1 through 8 (of 8 total)

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