Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Replace Expand / Collapse
Author
Message
Posted Tuesday, March 23, 2010 5:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 2:04 AM
Points: 72, Visits: 92
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?
Post #887999
Posted Tuesday, March 23, 2010 6:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
; WITH cteTableName AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY Man_name ORDER BY Model ) RowNum, *
FROM TableName
)

SELECT CASE WHEN RowNum > 1 THEN '' ELSE Man_name END Man_name, Model
FROM cteTableName

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/
Post #888022
Posted Tuesday, March 23, 2010 6:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:53 PM
Points: 2,112, Visits: 5,476
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/
Post #888024
Posted Tuesday, March 23, 2010 6:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 2:04 AM
Points: 72, Visits: 92
Hi Kingston,

It is working. Just give me small explanation how this code is working..... If you explain it would be great help.
Post #888045
Posted Tuesday, March 23, 2010 6:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 2:04 AM
Points: 72, Visits: 92
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?
Post #888048
Posted Tuesday, March 23, 2010 6:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
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/
Post #888069
Posted Tuesday, March 23, 2010 7:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 2:04 AM
Points: 72, Visits: 92
Thanks Kingston..

Meet you with another problem.....

Bye!!!
Post #888081
Posted Wednesday, October 2, 2013 10:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
Hi Friends,

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

Can any one help with replace command?

This statement is not getting replaced.

Thanks
Shobana
Post #1500980
Posted Wednesday, October 2, 2013 10:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:10 AM
Points: 1,151, Visits: 82
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')
Post #1500983
Posted Wednesday, October 2, 2013 11:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 16, 2013 2:05 AM
Points: 17, Visits: 46
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
Post #1500987
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse