convert and replace at same time

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 2005 Forums

Transact-SQL (2005)

convert and replace at same time

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-03-04 : 13:46:21

i saw the previous query in which it replaces null values to '-'
i also want to do same but i have quantity column which is numeric(38,5) and i have to convert it first to varchar to replace null values of quantity - to '-'

select when quantity is null then cast(quantity as varchar)+ '-' else quantity from saleshistory

but still its getting nulls.

mkool
Starting Member

25 Posts

Posted - 2008-03-04 : 13:51:43

i did like

select replace(cast(quantity as varchar),null,'-') from saleshistory

but still getting nulls

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 14:15:32

Numeric columns CAN'T have a single "-" in it.

UPDATE YourTable
SET YourColumn = 0.0
WHERE YourColumn IS NULL

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

mkool
Starting Member

25 Posts

Posted - 2008-03-04 : 14:28:46

saleshistory is view not table

so when i m updating
Update or insert of view or function 'saleshistory' failed because it contains a derived or constant field.

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-03-04 : 14:39:25

Try like this :

select case when cast(quantity as varchar) is null then '---' else cast(quantity as varchar) end from saleshistory

mkool
Starting Member

25 Posts

Posted - 2008-03-04 : 14:44:32

select case when cast(quantity as varchar) is null then '---' else cast(quantity as varchar) end from saleshistory

Got the perfect results which i want.

Thank you so much Peso and Sqlhelp14.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-04 : 19:35:42

select ISNULL(cast(quantity as varchar),'---') from saleshistory

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 03:32:24

I always prefer to specify the column length during convertion
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

mkool
Starting Member

25 Posts

Posted - 2008-03-05 : 08:45:18

Thanks Jeff Moden for replying.

and thanks a lot!!! madhivanan for column length during convertion blogcast..always remember. thanks!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-05 : 08:51:50

This is a presentation layer, you absolutely should not be doing this in T-SQL. Where are you outputting/displaying this data? It is there that you should handle formatting. By doing this in T-SQL, you must convert all of your numeric data to VARCHAR's, which means they are no longer valid numerics. You are making things slower and more difficult on yourself by trying to use the database to format output.

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-05 : 11:46:48

Heh...SHHH! I make a living at fixing that kind of stuff...

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources