October 20, 2015 at 2:00 pm
using the splitter referenced in my signature line, you could create this function:
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS RETURN
SELECT NewString =
(
SELECT DISTINCT item+' '
FROM DelimitedSplit8K(@string,' ')
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
GO
Then use it like this:
DECLARE @table TABLE (MyString varchar(1000));
INSERT @table VALUES ('BLACKHEATH 0AA BLACKHEATH COLCHESTER CO2 0AA')
SELECT *
FROM @table
CROSS APPLY dbo.RemoveDupes(MyString)
-- Itzik Ben-Gan 2001
October 20, 2015 at 2:10 pm
Alan, your idea is good, but if order is important a tweak might be needed.
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS
RETURN
WITH CTE AS(
SELECT MIN( ItemNumber) ItemNumber,
Item
FROM DelimitedSplit8K( @String, ' ') s
GROUP BY Item
)
SELECT NewString =
(
SELECT Item + ' '
FROM CTE
ORDER BY ItemNumber
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
October 20, 2015 at 2:16 pm
its not working 🙁 my tbl name is tblemp column name is empaddress
October 20, 2015 at 2:17 pm
Luis Cazares (10/20/2015)
Alan, your idea is good, but if order is important a tweak might be needed.
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS
RETURN
WITH CTE AS(
SELECT MIN( ItemNumber) ItemNumber,
Item
FROM DelimitedSplit8K( @String, ' ') s
GROUP BY Item
)
SELECT NewString =
(
SELECT Item + ' '
FROM CTE
ORDER BY ItemNumber
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
I was thinking about that. My strategy was to hope that order was not important. 😉
-- Itzik Ben-Gan 2001
October 20, 2015 at 2:19 pm
mr.addikhan (10/20/2015)
its not working 🙁 my tbl name is tblemp column name is empaddress
I would suggest taking what I posted (or what Luis posted if order is important) and changing the table name and column name accordingly.
-- Itzik Ben-Gan 2001
October 20, 2015 at 2:22 pm
can u please tell me in detail because , its important for me..
i have three column , empid , empname, empaddress
empaddress data :''BLACKHEATH 0AA BLACKHEATH COLCHESTER CO2 0AA ''
i want remove duplicate word in my column like this 'BLACKHEATH COLCHESTER CO2 0AA'
please help me
October 20, 2015 at 2:24 pm
Alan.B (10/20/2015)
mr.addikhan (10/20/2015)
its not working 🙁 my tbl name is tblemp column name is empaddressI would suggest taking what I posted (or what Luis posted if order is important) and changing the table name and column name accordingly.
Thanks sir, i was try but not working
October 20, 2015 at 2:30 pm
mr.addikhan (10/20/2015)
Alan.B (10/20/2015)
mr.addikhan (10/20/2015)
its not working 🙁 my tbl name is tblemp column name is empaddressI would suggest taking what I posted (or what Luis posted if order is important) and changing the table name and column name accordingly.
Thanks sir, i was try but not working
This seems to work:
/**********************************************************
(1) Create what we think your environment looks like
**********************************************************/
-- creating a partial replica of your table
CREATE TABLE tblemp (empaddress varchar(1000))
-- inserting the sample value you posted
INSERT tblemp VALUES ('BLACKHEATH 0AA BLACKHEATH COLCHESTER CO2 0AA')
/**********************************************************
(2) Example of how to use the RemoveDupes function
**********************************************************/
SELECT NewString
FROM tblemp
CROSS APPLY dbo.RemoveDupes(empaddress)
GO
What happens if you try just this?
SELECT NewString
FROM tblemp
CROSS APPLY dbo.RemoveDupes(empaddress);
-- Itzik Ben-Gan 2001
October 20, 2015 at 2:37 pm
i am using SQL Server 2008
step 1 : i am create your posted function like this
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS RETURN
SELECT NewString =
(
SELECT DISTINCT empaddress+' '
FROM tblemp(@string,' ')
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
GO
its show error : Msg 215, Level 16, State 1, Procedure RemoveDupes, Line 6
Parameters supplied for object 'tblemp' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.
October 20, 2015 at 2:41 pm
Alan.B (10/20/2015)
mr.addikhan (10/20/2015)
Alan.B (10/20/2015)
mr.addikhan (10/20/2015)
its not working 🙁 my tbl name is tblemp column name is empaddressI would suggest taking what I posted (or what Luis posted if order is important) and changing the table name and column name accordingly.
Thanks sir, i was try but not working
This seems to work:
/**********************************************************
(1) Create what we think your environment looks like
**********************************************************/
-- creating a partial replica of your table
CREATE TABLE tblemp (empaddress varchar(1000))
-- inserting the sample value you posted
INSERT tblemp VALUES ('BLACKHEATH 0AA BLACKHEATH COLCHESTER CO2 0AA')
/**********************************************************
(2) Example of how to use the RemoveDupes function
**********************************************************/
SELECT NewString
FROM tblemp
CROSS APPLY dbo.RemoveDupes(empaddress)
GO
What happens if you try just this?
SELECT NewString
FROM tblemp
CROSS APPLY dbo.RemoveDupes(empaddress);
thanks for your answer , thanks but
i am using SQL Server 2008
step 1 : i am create your posted function like this
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS RETURN
SELECT NewString =
(
SELECT DISTINCT empaddress+' '
FROM tblemp(@string,' ')
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
GO
its show error : Msg 215, Level 16, State 1, Procedure RemoveDupes, Line 6
Parameters supplied for object 'tblemp' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.
October 20, 2015 at 2:46 pm
You forgot to create the splitter in your system. You need to use it inside the function and call the function using your table.
October 20, 2015 at 2:48 pm
Sir, i am junior developer i am learning sql server, your professional i need only help..:crying:
October 20, 2015 at 2:56 pm
Luis Cazares (10/20/2015)
You forgot to create the splitter in your system. You need to use it inside the function and call the function using your table.
thanks for your answer , thanks but
i am using SQL Server 2008
step 1 : i am create your posted function like this
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS RETURN
SELECT NewString =
(
SELECT DISTINCT empaddress+' '
FROM tblemp(@string,' ')
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
GO
its show error : Msg 215, Level 16, State 1, Procedure RemoveDupes, Line 6
Parameters supplied for object 'tblemp' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.
October 20, 2015 at 3:09 pm
Luis Cazares (10/20/2015)
Alan, your idea is good, but if order is important a tweak might be needed.
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS
RETURN
WITH CTE AS(
SELECT MIN( ItemNumber) ItemNumber,
Item
FROM DelimitedSplit8K( @String, ' ') s
GROUP BY Item
)
SELECT NewString =
(
SELECT Item + ' '
FROM CTE
ORDER BY ItemNumber
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
its show error 🙁 🙁 🙁
CREATE FUNCTION dbo.RemoveDupes(@string varchar(8000))
RETURNS TABLE AS
RETURN
WITH CTE AS(
SELECT MIN( empaddress) empname,
empaddress
FROM tblemp( @String, ' ') s
GROUP BY empname
)
SELECT NewString =
(
SELECT empaddress + ' '
FROM CTE
ORDER BY empname
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)');
October 20, 2015 at 4:14 pm
mr.addikhan (10/20/2015)
Sir, i am junior developer i am learning sql server, your professional i need only help..:crying:
That's even a better reason to stop and understand what you're doing. You need to understand the difference between a table (in this case tblemp) and a table valued function (in this case DelimitedSplit8k and RemoveDupes). Tables don't take parameters, they're just an organized and structured container for data. Functions return values, either scalar or tables.
Go to the following article and read it before downloading and executing code that you don't understand. http://www.sqlservercentral.com/articles/Tally+Table/72993/
Read also the following article (much shorter) to understand the concatenation method used in the posted solutions: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Ask any questions that you might have after reading the articles. If you need to go to more basic stuff, try investigating the different objects available in SQL Server (tables, functions, stored procedures, views, etc.).
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply