select where left in left

[anselme]

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

[skhanal]

Try this

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)
)

[anselme]

thank you

it works with

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