MS SQL: duplicates with DISTINCT
MS SQL: duplicates with DISTINCT
(OP)
SELECT DISTINCT
LTRIM(RTRIM(queryParam)) AS qpTrimmed
FROM queryParamValTbl
has duplicates (it removes most duplicates)
I assumed that
scngs_zone
scngs_zone
was really
'scngs_zone'
'scngs_zone ' -- trailing whitespace
So I tried the following:
SELECT
DISTINCT
LTRIM(RTRIM(queryParam)) AS qpTrimmed
INTO #rTmp
FROM queryParamValTbl
ORDER BY qpTrimmed
SELECT DISTINCT * FROM #rTmp
-- But it has the exact same duplicates
Note, most duplicates are removed.
SQL 2000 & Win03 - lastest updates on each
LTRIM(RTRIM(queryParam)) AS qpTrimmed
FROM queryParamValTbl
has duplicates (it removes most duplicates)
I assumed that
scngs_zone
scngs_zone
was really
'scngs_zone'
'scngs_zone ' -- trailing whitespace
So I tried the following:
SELECT
DISTINCT
LTRIM(RTRIM(queryParam)) AS qpTrimmed
INTO #rTmp
FROM queryParamValTbl
ORDER BY qpTrimmed
SELECT DISTINCT * FROM #rTmp
-- But it has the exact same duplicates
Note, most duplicates are removed.
SQL 2000 & Win03 - lastest updates on each





RE: MS SQL: duplicates with DISTINCT
RE: MS SQL: duplicates with DISTINCT
RTRIM
Returns a character string after truncating all trailing blanks.
But I don't have blanks, I have '\r\n'
apparently there is no T-SQL function to remove white space.
RE: MS SQL: duplicates with DISTINCT
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: MS SQL: duplicates with DISTINCT
It's such a common problem every high level language has the Trim methods to deal with it. Here is how I solved the problem.
CREATE FUNCTION xTRIM (@rVarParam VARCHAR(1024))
RETURNS VARCHAR(1024)
AS
BEGIN
RETURN LTRIM(RTRIM(
REPLACE(REPLACE(@rVarParam,CHAR(10),''),CHAR(13),'')
))
END
Then changed my Query to:
SELECT DISTINCT dbo.xTRIM(queryParam)
FROM queryParamValTbl
WHERE queryParamValue
NOT IN ( SELECT queryName FROM queryNameTbl)
RE: MS SQL: duplicates with DISTINCT
T-SQL deals with ASCII strings
Yes, and unicode too. See nvarchar