Replace "Zeros Only" with Blank

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

SQL Server 2000 Forums

Transact-SQL (2000)

Replace "Zeros Only" with Blank

Author

Topic

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 06:12:18

Hi Team

I have the below section of a query:

t1.[Contract No_], REPLACE(t1.[Contract Line No_], '0','') as [Contract Line No_],

The results make all zero's disappear but I only need the zeros where there is a '0'value and not a '200000' or 01500' it changes it to a '2' and '15'.

Please help. Many thanks

Pom

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 06:45:28

case when t1.[Contract No_] not like '%[^0 ]%' then REPLACE(t1.[Contract Line No_], '0','') else t1.[Contract Line No_] end

that will replace anything with only 0's and spaces and leave everything else alone.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 07:23:35

Im getting the following now:

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ','.

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 07:24:32

This is the entire query:

select
REPLACE(REPLACE(t1.[Invoice],'0','No'),'1','Yes') as [Invoice],
REPLACE(REPLACE(t1.[Invoiced],'0','No'),'1','Yes') as [Invoiced],
REPLACE(REPLACE(t1.[Posted],'0','No'),'1','Yes') as [Posted],
t1.[Sub Contract No_],
t1.[Business-with No_],
t1.[Document No_]as "Order Number",
t1.[Invoice Layout Code],
REPLACE(t1.[Qty_ to Dispose], '0','') as [Qty_ to Dispose],
t1.[Contract No_],
t1.[Contract Line No_], case when t1.[Contract Line No_] not like '%[^0 ]%' then REPLACE(t1.[Contract Line No_], '0','') else t1.[Contract Line No_],
t1.[Business with Name],
t2.[Task-at Name] as "Task At Name (Header)",
REPLACE(REPLACE(REPLACE(t1.[Posting Type],'0',''),'1','Purchase'),'2','Sales') AS [Posting Type],
t1.[Post-with No_],
t1.[Invoice-with No_],
REPLACE(REPLACE(t1.[Type],'0',''),'1','Service') as [Type],
t1.[No_],
t1.[Description],
t1.[Int_ Material Catalog]as "Waste Type",
t1.[Quantity],
t1.[Unit of Measure],
'£' + CONVERT(varchar, t1.[Unit Price], 1) AS [Unit Price],
'£' + convert(varchar,t1.[Amount]) as [Amount],
'£' + convert(varchar,t1.[Amount Including VAT]) as [Amount Including VAT],
CONVERT(varchar,t2.[Task Date],111) as "Task Date",
CONVERT(varchar,t2.[Order Date],111) as "Order Date",
CONVERT(varchar,t2.[Document Date],111) as "Document Date",
left (convert (varchar,t2.[Order Date],120),7) as [Month]
from DHL.dbo.[DHL Waste$Waste Management Header] t2
inner join DHL.dbo.[DHL Waste$Waste Management Line] t1
on t2.[No_] = t1.[Document No_]
where t1.[Document Type] = 5
and t2.[Order Date]= '2011/10/01'
Order By t2.[No_] ASC

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 07:24:55

Have a look at line 11 - or post the query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 07:25:45

You've missed the end.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 07:36:02

The results still show the zeros in the grid:

Yes Yes Yes 01 CU-000565 SO-091793 DEFAULT . WC-000500 30000 30000
Yes Yes Yes 01 CU-000082 SO-091793 DEFAULT . WC-000500 40000 40000
Yes Yes Yes 01 CU-000565 SO-091793 DEFAULT . WC-000500 50000 50000
No No No SO-091889 . 0 0
Yes Yes Yes CU-000464 SO-091889 DEFAULT . 0 0
Yes Yes Yes CU-000464 SO-091889 DEFAULT . 0 0

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 07:41:01

Also my REPLACE(t1.[Qty_ to Dispose], '0','') as [Qty_ to Dispose], line is placing '.' in the grid results area, I also need for this to be a blank. (Im new to this stuff and im learning from a book and this forum.)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 08:44:58

What is the actual string you are trying to replace?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-30 : 10:08:39

Im trying to show all zeros as a blank, so when I copy over to an Excel template the zeros dont show.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 11:29:33

That is replace(col, '0',' ') - so 10102 turns into 1 1 2
but I suspect that isn't really what you want.

You know you can do the display formatting in excel?

Give examples - I think this is requirements definition issue rather than a codeing issue.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources