REPLACEMENT PROBLEM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

REPLACEMENT PROBLEM

Author

Topic

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-24 : 21:33:24

I wish to replace column2 'ABC' value become blank.

EXAMPLE TABLE VIEW

column2
-------
ABC 123, Alt. ABC 456, Alt ABC 789

select replace(replace(B.[column 2],A.[column 1],''),'ABC','')
from TABLE A,TABLE B
where A.id = B.id

After executed

column2
-------
123, Alt. 456, Alt. 789

But not expected result, because i jz wan ABC become blank not Alt. ABC.

So what should i modify with my logic and script?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-24 : 23:40:29

Hi,

Try like this

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select REplace (@str,'ABC ','')

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-24 : 23:56:43

quote:
Originally posted by ranganath

Hi,

Try like this

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select REplace (@str,'ABC ','')

thx 4 guiding.

Msg 8152, Level 16, State 10, Line 2
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)

i got more than 100 rows record, but jz 1 row affected?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-25 : 00:01:07

Hi,

Take @Str Varchar(Max)

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 00:15:18

quote:
Originally posted by ranganath

Hi,

Take @Str Varchar(Max)

thx 4 guiding..
tried. but result same as well unexpected..

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-25 : 01:47:28

Could you post the exact sql statement you are using ?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 01:54:44

quote:
Originally posted by jackv

Could you post the exact sql statement you are using ?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

select replace(replace(B.[column 11],A.DOB,''),'DOB','')
from list_bak A,list B
where A.primaryuid = B.[column 0]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 03:13:21

How about this?

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select CASE WHEN PATINDEX('%ABC%',@str)=1 THEN REplace (@str,'ABC ','') ELSE @str END

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 03:38:23

quote:

Originally posted by visakh16

How about this?

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select CASE WHEN PATINDEX('%ABC%',@str)=1 THEN REplace (@str,'ABC ','') ELSE @str END

thx 4 another solution, but still not done yet...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 03:50:24

quote:
Originally posted by ooi_happiness

quote:
Originally posted by visakh16

How about this?

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select CASE WHEN PATINDEX('%ABC%',@str)=1 THEN REplace (@str,'ABC ','') ELSE @str END

thx 4 another solution, but still not done yet...

What was result got?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 03:52:22

[code]-- Prepare sample data. This is not part of the solution, only to mimic your environment.
DECLARE @B TABLE (ID INT, Column2 VARCHAR(100))

INSERT @B
SELECT 1, 'ABC 123, Alt. ABC 456, Alt ABC 789'

DECLARE @A TABLE (ID INT, Column1 VARCHAR(100))

INSERT @A
SELECT 1, 'ABC'

-- Show original value for table b
SELECT *
FROM @B

-- This is a suggestion for a solution
UPDATE b
SET b.Column2 = REPLACE(b.Column2, a.Column1 + ' ', '')
FROM @B AS b
INNER JOIN @A AS a ON a.ID = b.ID

-- Show new value for table b
SELECT *
FROM @B[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-25 : 03:55:47

quote:
Originally posted by ooi_happiness

quote:
Originally posted by visakh16

How about this?

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select CASE WHEN PATINDEX('%ABC%',@str)=1 THEN REplace (@str,'ABC ','') ELSE @str END

thx 4 another solution, but still not done yet...

Can you post the query that throwed error?

Madhivanan

Failing to plan is Planning to fail

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 04:45:09

quote:
Originally posted by visakh16

quote:
Originally posted by ooi_happiness

quote:
Originally posted by visakh16

How about this?

DEclare @str varchar(100)
Set @str = 'ABC 123, Alt. ABC 456, Alt ABC 789 '
Select CASE WHEN PATINDEX('%ABC%',@str)=1 THEN REplace (@str,'ABC ','') ELSE @str END

thx 4 another solution, but still not done yet...

What was result got?

same as well system removed all the word we trace.

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 04:51:17

quote:

Originally posted by Peso

-- Prepare sample data. This is not part of the solution, only to mimic your environment.
DECLARE @B TABLE (ID INT, Column2 VARCHAR(100))

INSERT	@B
SELECT	1, 'ABC 123, Alt. ABC 456, Alt ABC 789'

DECLARE	@A TABLE (ID INT, Column1 VARCHAR(100))

INSERT	@A
SELECT	1, 'ABC'

-- Show original value for table b
SELECT	*
FROM	@B

-- This is a suggestion for a solution
UPDATE		b
SET		b.Column2 = REPLACE(b.Column2, a.Column1 + ' ', '')
FROM		@B AS b
INNER JOIN	@A AS a ON a.ID = b.ID

-- Show new value for table b
SELECT	*
FROM	@B

E 12°55'05.25"
N 56°04'39.16"

Same result

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-25 : 04:58:16

Hi, try this

DECLARE @D VARCHAR(MAX)
SELECT @D = 'ABC 123, Alt. ABC 456, Alt ABC 789'

SELECT @D, RTRIM(LTRIM(SUBSTRING(@D, PATINDEX('%[^ABC]%', @D), 5000)))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 04:58:49

You want to blank complete field (column value) if the two tables matches?
You are not very clear what "blank" means to you.

-- Prepare sample data. This is not part of the solution, only to mimic your environment.
DECLARE @B TABLE (ID INT, Column2 VARCHAR(100))

INSERT	@B
SELECT	1, 'ABC 123, Alt. ABC 456, Alt ABC 789'

DECLARE	@A TABLE (ID INT, Column1 VARCHAR(100))

INSERT	@A
SELECT	1, 'ABC'

-- Show original value for table b
SELECT	*
FROM	@B

-- This is a suggestion for a solution
UPDATE		b
SET		b.Column2 = ''
FROM		@B AS b
INNER JOIN	@A AS a ON a.ID = b.ID
WHERE		b.Column2 LIKE '%' + a.Column1 + '%'

-- Show new value for table b
SELECT	*
FROM	@B

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-25 : 06:34:37

DECLARE @T TABLE ( Id INT, Sr VARCHAR(MAX), XML XML)

INSERT INTO @T(Id, Sr)
SELECT 1, 'ABC 456,Alt. ABC 123,Alt ABC 789' UNION ALL
SELECT 2, 'Alt. ABC 123,ABC 456,Alt ABC 789' UNION ALL
SELECT 3, 'Alt. ABC 123,Alt ABC 789,ABC 456,ABC ALT,ABC ABC'

UPDATE @T
SET XML = '<d>' + REPLACE(Sr, ',', '</d><d>') + '</d>'

SELECT Id,
STUFF((SELECT ', ' + LTRIM(RTRIM(CASE WHEN PATINDEX('%[^ABC][^0-9]%', T2.Split.value('.', 'VARCHAR(100)') ) = 0 THEN REPLACE(T2.Split.value('.', 'VARCHAR(100)'), 'ABC', '') ELSE T2.Split.value('.', 'VARCHAR(100)') END))
FROM @T
CROSS APPLY XML.nodes('/d') as T2(Split)
WHERE Id = T.Id
FOR XML PATH ('')
), 1, 2, '') AS Val
FROM @T T

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 20:13:11

quote:
Originally posted by Peso

You want to blank complete field (column value) if the two tables matches?
You are not very clear what "blank" means to you

im assigning two tables id matches because, i need to clear something with A table DOB column(This done successfully).
Then the values in [column 11] that wish to instead with "BLANK", is only 'DOB' = ' ' but ALT. DOB remain the same.

select replace(replace(B.[column 11],A.DOB,''),'DOB','')
from list_bak A,list B
where A.primaryuid = B.[column 0]

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-25 : 20:24:33

quote:
Originally posted by PeterNeo

HOORAY!!!!!!!!!
PETERNEO,
thanks a lot a lot a lot... your script are really efficient!
the script is point to values we assign ourself, then im failing in if i move the point refer to column.

EXAMPLE:
DECLARE @D VARCHAR(MAX)
SELECT @D = 'ABC 123, Alt. ABC 456, Alt ABC 789'

BECOME :
DECLARE @D VARCHAR(MAX)
SELECT @D = column_name

EXAMPLE
DECLARE @T TABLE ( Id INT, Sr VARCHAR(MAX), XML XML)
INSERT INTO @T(Id, Sr)
SELECT 1, 'ABC 456,Alt. ABC 123,Alt ABC 789' UNION ALL

BECOME :
DECLARE @T TABLE ( Id INT, Sr VARCHAR(MAX), XML XML)
INSERT INTO @T(Id, Sr)
SELECT 1, column_name UNION ALL

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-26 : 00:13:19

hi,

DECLARE @T TABLE ( Id INT, Sr VARCHAR(MAX), XML XML)
INSERT INTO @T(Id, Sr)
select A.id,replace(B.[column 2],A.[column 1],'')
from TABLE A,TABLE B
where A.id = B.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources