Replace

Question

Post reply

Replace

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
March 23, 2010 at 5:34 am

#220300

Hi
Consider this table
Man_name Model
Motorola MotoQ
Motorola RAZR2
Nokia N73
Nokia 6100
Samsung C118
I have used Distinct Statement to retrieve the value.
Now I want to replace if the Man_Name comes 2nd time then I need to replace that value with "Blank".
Is it possible to do this?

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

; WITH cteTableName AS

(

SELECTROW_NUMBER() OVER( PARTITION BY Man_name ORDER BY Model ) RowNum, *

FROMTableName

)

SELECTCASE WHEN RowNum > 1 THEN '' ELSE Man_name END Man_name, Model

FROMcteTableName

This should do it for you..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

How do you define which record is the second record? When you have 2 values do you have a rule that according to it you know which one to update and which record to leave as it is? Assuming that you do it by a key, here is a small example of how to do it:

use tempdb

go

declare @tbl table (id int not null primary key, vc varchar(10))

insert into @tbl (id, vc)

select 1, 'aaa'

union

select 2, 'bbb'

union

select 3, 'aaa'

union

select 4, 'aaa'

union

select 5, 'ccc';

with NotToUpdate as(

select MIN(id) as id, vc

from @tbl

group by vc)

update t

set t.vc = ''

from @tbl t left join NotToUpdate NTU on t.id = NTU.id

where NTU.id is null

select * from @tbl

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 3

Hi Kingston,

It is working. Just give me small explanation how this code is working..... If you explain it would be great help.

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 4

You are spliting the data by Partition and assigning row numbers.

After that for row_number> 1 you are assiging blank right.

Is my understanding is correct?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

You are right:-)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 6

Thanks Kingston..

Meet you with another problem.....

Bye!!!

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 7

Hi Friends,

replace('$filter', 'itime', 'am.itime')

Can any one help with replace command?

This statement is not getting replaced.

Thanks

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 8

Hi,

Here '$filter' word does not contains 'itime' thats why it is not being replaced.

Check below statement for your reference.

select REPLACE('abcdef','ef','EF')

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 9

Thanks for your immediate reply.................

in $filter they are using conditions like itime>= and itime<=......

select distinct split_part(split_part(cp.msg, 'src=',1),',',1),split_part(split_part(cp.msg, 'user=''',2),',',1) as user ,am.itime,am.dtime,am.url from "FGT60B3908669009-wlog-1380091698" am join "SYSLOG-C0A80A41-glog-1380347892" cp on cast(am.src as character varying)=cast((split_part(split_part(cp.msg, 'src=',2),',',1) ) as character varying) where am.src='192.168.10.23' and replace('$filter', 'itime', 'am.itime')

This is my query......

if the user what 1 day reports it takes long time for querying because of large data..... so i want to replace itime with my am.itime in $ filter............

for more details pls come online in gmail.(shobanapraveen80@gmail.com)

Thanks,

Shobana

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 10

Instead of using replace use CASE WHEN ... THEN END syntex

like

and $filter = CASE WHEN CONDITION THEN 'VALUE' ELSE 'VALUE' END

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 11

Instead of using replace use CASE WHEN ... THEN END syntex

like

and $filter = CASE WHEN CONDITION THEN 'VALUE' ELSE 'VALUE' END

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 12

what this command will do?

whether it gives report for n timestamp(say 1h,one day like that)?

Thanks

Shobana

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 13

With the help of my query can you add your CASE command and show me the query?

Thanks

Shobana

shobanapraveen SSC-Addicted Points: 459 More actions · Answer 14

ERROR: argument of AND must be type boolean, not type text.

LINE 1: ...aracter varying) where am.src='192.168.10.23' and replace('i...

when i run that query i am getting above error...............

its urgent

Can you help me out..............:ermm:

Thanks,

Shobana