replace same more then one character in one column sql query

Question

Post reply

replace same more then one character in one column sql query

mr.addikhan

Old Hand

Points: 383
More actions
October 20, 2015 at 1:23 pm

#305785

Replace same more then one character in one column in sql.
Data column: 'BLACKHEATH 0AA BLACKHEATH COLCHESTER CO2 0AA '
But I want like this
BLACKHEATH COLCHESTER CO2 0AA
please help i want function and query

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mr.addikhan Old Hand Points: 383 More actions · Answer 3

its not working 🙁 my tbl name is tblemp column name is empaddress

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

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. 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mr.addikhan Old Hand Points: 383 More actions · Answer 6

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

mr.addikhan Old Hand Points: 383 More actions · Answer 7

Alan.B (10/20/2015)
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.

Thanks sir, i was try but not working

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

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

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mr.addikhan Old Hand Points: 383 More actions · Answer 9

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.

mr.addikhan Old Hand Points: 383 More actions · Answer 10

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

You forgot to create the splitter in your system. You need to use it inside the function and call the function using your table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mr.addikhan Old Hand Points: 383 More actions · Answer 12

Sir, i am junior developer i am learning sql server, your professional i need only help..:crying:

mr.addikhan Old Hand Points: 383 More actions · Answer 13

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.

mr.addikhan Old Hand Points: 383 More actions · Answer 14

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