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