April 26, 2006 at 8:29 am
Hello,
I want to avoid using cursors, however I am stuck in this problem:
I have two tables.
In table1 there is a VARCHAR column T1A that contains strings.
In table2 there is also a VARCHAR column T2A that contains strings.
I want to delete all words that appear in T2A from T1A. But without using cursors.
So for example:
Table1:
VARCHAR COLUMN T1A
"This is a text"
"And this appears in row two"
"Hello World"
"I like T-SQL"
"The world is great."
Table2:
VARCHAR COLUMN T2A
"World"
"Appears"
Now I am looking for a cursorless query that returns the following:
"This is a text"
"And this in row two"
"Hello "
"I like T-SQL"
"The is great."
So the words 'World' and 'Appears' are removed from table 1 COLUMN 1TA
Can somebody help me??
Thank you all very much!!
Erik
April 26, 2006 at 8:59 am
hi Harry,
the select to replace the words is quite straightforward :-
select replace(replace(lower(T1.T1A), lower(T2.T2A),""), " ", " ")
from table1 T1, table2 T2
where lower(T1.T1A) like "%" + lower(T2.T2A) + "%"
i've used table1 and table2 as table names, and a second replace to replace the double spaces left with a single space.
However you may need to copy the original rows to a temp table, perform the above select as an update to that temp table, and then select all the rows from that to get the output of all 5 rows (i can't think of an easy way to do it all in the one select)
Hope this helps
Paul
April 26, 2006 at 9:06 am
could try this (will work with one match per row)
set nocount on
create table x ( t VARCHAR (100))
insert x (t) values ('This is a text')
insert x (t) values ('And this appears in row two')
insert x (t) values ('Hello World')
insert x (t) values ('I like T-SQL')
insert x (t) values ('The world is great.')
create table z(t2 VARCHAR (100))
insert z (t2) values ('World')
insert z (t2) values ('Appears')
go
select replace(t,
coalesce((select t2 from z where t like ('%' + t2 + '%')),'')
,'')
from
x
go
drop table x
go
drop table z
Phil Nicholas
April 26, 2006 at 9:53 am
You will not need to use a temp table. This will work:
CREATE TABLE T1A (TextColumn varchar(1000))
CREATE TABLE T2A (TextColumn varchar(1000))
INSERT INTO T1A
SELECT 'This is a text' UNION ALL
SELECT 'And this appears in row two' UNION ALL
SELECT 'Hello World' UNION ALL
SELECT 'I like T-SQL' UNION ALL
SELECT 'The world is great.'
INSERT INTO T2A
SELECT 'World' UNION ALL
SELECT 'Appears'
UPDATE T1A
SET T1A.TextColumn = REPLACE(REPLACE(T1a.TextColumn,T2a.TextColumn,''),' ','')
FROM T1A
LEFT OUTER JOIN T2A
ON CHARINDEX(T2A.TextColumn,T1A.TextColumn) > 0
WHERE CHARINDEX(T2A.TextColumn,T1A.TextColumn) > 0
April 26, 2006 at 9:54 am
Hi,
I am always tempted to use cursors....
Thank you very much, this is of great help to me!!!
Greetings,
Erik
April 26, 2006 at 10:06 am
This will eliminate problem of 2 key words in a record
create table x ( t VARCHAR (100))
insert x (t) values ('This is a text')
insert x (t) values ('And this appears in row two')
insert x (t) values ('Hello World')
insert x (t) values ('I like T-SQL')
insert x (t) values ('The world is great.')
create table z(id int IDENTITY(0,1), t2 VARCHAR (100))
insert z (t2) values ('World')
insert z (t2) values ('Appears')
go
CREATE FUNCTION removeWords (@t varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @id int
DECLARE @idMax int
SET @id = (SELECT MIN(id) FROM z)
SET @idMAX = (SELECT MAX(id) FROM z)
WHILE @id <= @idMAX
BEGIN
SET @t = REPLACE(@t, (SELECT t2 FROM z WHERE id = @id) + ' ', '')
SET @id = @id + 1
END
RETURN (SELECT @t)
END
GO
SELECT dbo.removeWords(t) t_fixed FROM x
GO
drop function removeWords
go
drop table x
go
drop table z
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply