Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: select where left in left

    hello

    for MS SQL 2000, i cannot get it

    i am having names like AB_12 I want to get all rows with left part similar , AB im that case


    SELECT id, name
    FROM Users
    WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN
    (
    SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
    FROM Users
    GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
    HAVING (COUNT(*) > 1)
    )



    does not work



    is there any way to use a variable ?

    declare @nm nvarchar
    set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
    FROM Users


    thank you for helping

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT id, name
    FROM Users
    WHERE LEFT(name, CHARINDEX('_', name) - 1) IN
    (
    SELECT LEFT(name, CHARINDEX('_', name) - 1)
    FROM Users
    GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
    HAVING COUNT(*) > 1
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    i am getting a error message :
    Invalide length parameter passed to the substring function

    if I try

    SELECT LEFT(name, CHARINDEX('_', name) - 1)
    FROM Users
    GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
    HAVING COUNT(*) > 1


    it works

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    replace this --

    WHERE LEFT(name, CHARINDEX('_', name) - 1) IN

    with this --

    WHERE CHARINDEX('_', name) > 0
    AND LEFT(name, CHARINDEX('_', name) - 1) IN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    thank you r937

    it works

Posting Permissions

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