[RESOLVED] Query: help me!! - 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

SQL Server 2000 Forums

Transact-SQL (2000)

[RESOLVED] Query: help me!!

Author

Topic

dan_coder
Starting Member

20 Posts

Posted - 2006-10-20 : 05:16:50

Hi guys.

I have a table containg a nVarChar field called Price. This contain values like 12,42423 and, sometimes, wrong values like 'Milan', 'Rome' ecc..
-----------------------------
PRICE
-----------------------------
12,4545454
-----------------------------
15,46564
-----------------------------
Rome
-----------------------------
35,4456664
-----------------------------
Milan
-----------------------------

I want to perform a query that ectract a nVarChar field containg all numeric values with precisione 2, and the same values for wrong values.

-----------------------------
PRICE
-----------------------------
12,45
-----------------------------
15,46
-----------------------------
Rome
-----------------------------
35,44
-----------------------------
Milan
-----------------------------

Is there any way?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 05:40:11

[code]declare @tbl table
(
price varchar(200)
)

insert @tbl
select '12.4545454' union all
select '15.46564' union all
select 'Rome' union all
select '35.4456664' union all
select 'Milan'

select case when isnumeric(price)=1 then convert(varchar(200),convert(numeric(15,2), price)) else price end from @tbl[/code]

Harsh Athalye
India.
"Nothing is Impossible"

dan_coder
Starting Member

20 Posts

Posted - 2006-10-20 : 05:48:14

Thank you harsh_athalye, but data 12,4545454, Rome ecc was an eample only. I have more than 3000 records in my table!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-20 : 05:52:51

change the script to select from your table.

The declare & insert statement is just for testing the script

dan_coder
Starting Member

20 Posts

Posted - 2006-10-20 : 06:04:00

thank you guys, now it's ok!!!

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-20 : 06:09:13

Hi Harsh Athalye,
what if he is having too many rows, he has to union all?, this will take too much time, RIGHT?
i suggest him as (i am sure about his . and , notation, i mean what he want decimal or comma, in the post he shows the comma, so i go for that)

CREATE TABLE t (one nvarchar(30))
GO
INSERT INTO [dbo].[t] ([one]) VALUES ('PRICE')
INSERT INTO [dbo].[t] ([one]) VALUES ('12,4545454')
INSERT INTO [dbo].[t] ([one]) VALUES ('15,46564')
INSERT INTO [dbo].[t] ([one]) VALUES ('Rome')
INSERT INTO [dbo].[t] ([one]) VALUES ('35,4456664')
INSERT INTO [dbo].[t] ([one]) VALUES ('Milan')
INSERT INTO [dbo].[t] ([one]) VALUES ('Milan good')
INSERT INTO [dbo].[t] ([one]) VALUES ('1,2345')
INSERT INTO [dbo].[t] ([one]) VALUES ('123,45')
INSERT INTO [dbo].[t] ([one]) VALUES ('Gurpreet S. Gill')
INSERT INTO [dbo].[t] ([one]) VALUES ('23455,67')
GO

SELECT CASE WHEN patindex('%,%',one)>0 THEN substring (one,0,patindex('%,%',one)) + substring(one,patindex('%,%',one),3)else one end FROM t

Output:
PRICE
12,45
15,46
Rome
35,44
Milan
Milan good
1,23
123,45
Gurpreet S. Gill
23455,67

Enjoy
Regards,
Thanks.
Gurpreet S. Gill

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 07:29:55

Dear gurpreet,

the UNION part was just to create some sample data...it has nothing to do with actual solution. Actual query is the last one.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources