Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help required
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srinirao88
Starting Member

India
10 Posts

Posted - 03/10/2012 :  19:29:12  Show Profile  Reply with Quote
Hi All,

I am new to SQL, I am trying to replace a special character using the replace function, for one character it is working fine, but I am not able to replace multiple characters in one statement, please find the query below.

Select [col1],[col2], CONVERT(Float, replace([Col2],'-',''))

THIS WORKS FINE

BUT

Select [Col1] , [Col2],CONVERT(Float, replace([Col2],'-','','/',''))

There comes a error message as "The replace function requires 3 argument(s).", please help in resolving this.

Thanks in advance

Srinirao88

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/10/2012 :  20:35:50  Show Profile  Reply with Quote
use REPLACE() twice


CONVERT(Float, replace(replace([Col2], '-', '')), '/', '')



KH
Time is always against us


Edited by - khtan on 03/10/2012 20:36:59
Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/11/2012 :  09:22:52  Show Profile  Reply with Quote
Hi,
Thanks for the quick reply, I tried the above query still it is not able to compile it, it gives an error message "Incorrect syntax near the keyword 'CONVERT'", also it gives out a error message at return whn I place the cursor on it "incorrect syntax near 'replace'. expecting select, or'('.", also this appears for the second replace in the query.

For more info I am pasting the query below
Select [Service num],[col1] CONVERT(Float, replace([col1],'-',''))[col1],[col2] CONVERT(Float, replace([col2],'-',''))[col2] , [col3] CONVERT(Float, replace([col3],'-',''))[col3], [col4] CONVERT(Float, replace([col4],'-',''))[col4]   from table1 UNION ALL
Select [Ref #] , [col1] CONVERT(Float, replace(replace([col1],'-','')),'/','')[col1], [col2] CONVERT(Float, replace(replace([col2],'-','')),'/','')[col2], [col3] CONVERT(Float, replace(replace([col3],'-','')),'/','')[col3],[col4] CONVERT(Float, replace(replace([col4],'-','')),'/','')[col4] from table2


Looking forward for your reply.

Srinirao88
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/11/2012 :  09:47:26  Show Profile  Reply with Quote

Select 	[Service num],
	[col1] CONVERT(Float, replace([col1],'-',''))[col1],
	[col2] CONVERT(Float, replace([col2],'-',''))[col2], 
	[col3] CONVERT(Float, replace([col3],'-',''))[col3], 
	[col4] CONVERT(Float, replace([col4],'-',''))[col4]   
from 	table1 

UNION ALL

Select 	[Ref #] , 
	[col1] CONVERT(Float, replace(replace([col1],'-','')),'/','')[col1], 
	[col2] CONVERT(Float, replace(replace([col2],'-','')),'/','')[col2], 
	[col3] CONVERT(Float, replace(replace([col3],'-','')),'/','')[col3],
	[col4] CONVERT(Float, replace(replace([col4],'-','')),'/','')[col4] 
from 	table2



KH
Time is always against us

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/11/2012 :  11:12:28  Show Profile  Reply with Quote
Hi,

Tried the above query but it gives another error code tried to manipulate the query later there was another error message "Invalid object name Activity" and this error message is pointing to the column name inside the convert() function where as the same column name after the function provides no error message, please advise.

Please find the code attached below:

Select [Service_num], CONVERT(Float, replace([Activity1],'-',''))[Activity1],CONVERT(Float, replace([Activity2],'-',''))[Activity2] , CONVERT(Float, replace([Activity3],'-',''))[Activity3],CONVERT(Float, replace([Activity4],'-',''))[Activity4] from Activity UNION ALL

Select [SR #] , CONVERT(Float, replace([Diag1],'-',''),replace([Diag1],'/',''))[Diag1], CONVERT(Float, replace([Diag2],'-',''),replace([Diag2],'/',''))[Diag2], CONVERT(Float, replace([Diag3],'-',''),replace([Diag3],'/',''))[Diag3], CONVERT(Float, replace([Diag4],'-',''),replace([Diag4],'/',''))[Diag4] from SR


Srinirao88

Edited by - srinirao88 on 03/11/2012 11:14:23
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/11/2012 :  11:50:25  Show Profile  Reply with Quote
from Activity

is your table name "Activity" ?


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/11/2012 :  11:56:16  Show Profile  Reply with Quote
To OP
Try to post code within [code] tags so that its easier for others to read and understand the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/11/2012 :  12:09:36  Show Profile  Reply with Quote
@ khtan yes my table name is Activity and SR, I noticed that the same error message is appearing on the other query as ""Invalid object name Diag" when I place cursor on the query.

@visakh16 please find the code below with tags, thanks for the advise.

Select [Service_num], CONVERT(Float, replace([Activity1],'-',''))[Activity1],CONVERT(Float, replace([Activity2],'-',''))[Activity2] , CONVERT(Float, replace([Activity3],'-',''))[Activity3],CONVERT(Float, replace([Activity4],'-',''))[Activity4] from Activity UNION ALL 

Select [SR #] , CONVERT(Float, replace([Diag1],'-',''),replace([Diag1],'/',''))[Diag1], CONVERT(Float, replace([Diag2],'-',''),replace([Diag2],'/',''))[Diag2], CONVERT(Float, replace([Diag3],'-',''),replace([Diag3],'/',''))[Diag3], CONVERT(Float, replace([Diag4],'-',''),replace([Diag4],'/',''))[Diag4] from SR



Srinirao88

Edited by - srinirao88 on 03/11/2012 12:13:52
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/11/2012 :  22:06:22  Show Profile  Reply with Quote
you have the replace syntax in Diag wrong. Refer to my post on 03/10/2012 : 20:35:50

CONVERT(Float, replace(replace([Diag1], '-', '')), '/', '') as Diag1


Beside that, i don't see any issue with the query at all. Verify your table / column name.

Also run this very simple query.

SELECT [Service_num], [Activity1], [Activity2], [Activity3], [Activity4]
FROM   Activity

If there is any error, verify you are executing this in the correct database, schema etc

As far as i can see this query is fine, if the object name etc is correct

Select	[Service_num], 
	CONVERT(Float, replace([Activity1],'-',''))[Activity1],
	CONVERT(Float, replace([Activity2],'-',''))[Activity2] , 
	CONVERT(Float, replace([Activity3],'-',''))[Activity3],
	CONVERT(Float, replace([Activity4],'-',''))[Activity4] 
from 	Activity 



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/11/2012 :  22:10:33  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

To OP
Try to post code within [code] tags so that its easier for others to read and understand the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





in addition to that, you can also press [ENTER] key in strategic places ? I have short term memory, by the time, i scroll to the right, i have forgotten what i have just read. See how i re-post your query.


KH
Time is always against us

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/12/2012 :  00:43:46  Show Profile  Reply with Quote
Hi Khtan,

I did delete tables and database and tried to run the query individually, both the queries worked well, but when I tried to combine the queries using union All it gives out a error message "Error converting data type varchar to float."


Select Convert(varchar,[Service_num])[Service_num], CONVERT(varchar(MAX), replace([Act1],'-',''))[Act1],CONVERT(varchar(3000), replace([Act2],'-',''))[Act2] , CONVERT(varchar(3000), replace([Act3],'-',''))[Act3],CONVERT(varchar(3000), replace([Act4],'-',''))[Act4] from dbo.Act$ union All 

Select [SR ] , CONVERT(varchar, replace([diag1],'-',''))[diag1], CONVERT(varchar, replace([diag2],'-',''))[diag2],  CONVERT(varchar, replace([diag3],'-',''))[diag3], CONVERT(varchar, replace([diag4],'-',''))[diag4] from dbo.SR$


PS: I tried to paste the query using
it seems like it is not working here or I am not following the instructions...

Looking forward for your reply.

Srinirao88

Edited by - srinirao88 on 03/12/2012 00:44:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2012 :  00:46:49  Show Profile  Reply with Quote
quote:
Originally posted by srinirao88

Hi Khtan,

I did delete tables and database and tried to run the query individually, both the queries worked well, but when I tried to combine the queries using union All it gives out a error message "Error converting data type varchar to float."


Select Convert(varchar,[Service_num])[Service_num], CONVERT(varchar(MAX), replace([Act1],'-',''))[Act1],CONVERT(varchar(3000), replace([Act2],'-',''))[Act2] , CONVERT(varchar(3000), replace([Act3],'-',''))[Act3],CONVERT(varchar(3000), replace([Act4],'-',''))[Act4] from dbo.Act$ union All 

Select [SR ] , CONVERT(varchar, replace([diag1],'-',''))[diag1], CONVERT(varchar, replace([diag2],'-',''))[diag2],  CONVERT(varchar, replace([diag3],'-',''))[diag3], CONVERT(varchar, replace([diag4],'-',''))[diag4] from dbo.SR$


PS: I tried to paste the query using
it seems like it is not working here or I am not following the instructions...

Looking forward for your reply.

Srinirao88


it will work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/12/2012 :  02:46:22  Show Profile  Reply with Quote
Hi ,

Tried multiple times with different queries nothing worked, as I told the individual query is working but not the combined code for the union All it still gives the error code "Error converting data type nvarchar to float"

Also please let me know if there is any different way to combine two tables for a single output.




Srinirao88
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/12/2012 :  02:46:29  Show Profile  Reply with Quote
when you use UNION, you need to make sure the data type of the matching column need to be the same type, if not implicit conversion will kick in and you will get error.

for your union query, the 1st column from the 1st query is a varchar data type Convert(varchar,[Service_num])[Service_num]
what is the data type for [SR ] from the 2nd query ?

I would guess it is float !


KH
Time is always against us

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/12/2012 :  02:52:27  Show Profile  Reply with Quote
u were right!!!!
Data type for Service_num, Act1,Act2,Act3,Act4 is Nvarchar whereas for SR it is Float for diag1,diag2,diag3,diag4 it is Nvarchar, SR contains only numbers

Srinirao88
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/12/2012 :  04:09:52  Show Profile  Reply with Quote
then you have to convert SR to string explicitly


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 03/12/2012 :  04:11:32  Show Profile  Reply with Quote
quote:
Originally posted by khtan

then you have to convert SR to string explicitly

select convert(varchar(10), SR), . . .


KH
Time is always against us






KH
Time is always against us

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/12/2012 :  06:32:03  Show Profile  Reply with Quote
Hi,

That worked fine thanks but now the problem is in the result, i.e only the Table 1 columns were displayed and the table 2 columns were mixed along with the same result, i.e no columns were not created for Table2, is there a way to separate it or is there any other way of join instead of Union? Please find the query below:
Create view _View1 AS
select [Service_num], [Act Tier1],[Act Tier2],[Act Tier3],[Act Tier4] from  dbo.Act$ 

UNION ALL

select Convert(varchar(20),[SR])[SR],[Diag Tier1],[Diag Tier2],[Diag Tier3],[Diag Tier4] from SR$




Srinirao88
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2012 :  10:02:39  Show Profile  Reply with Quote
so you want columns of table1 abd table2 side by side? In that case, do you want rows from both table comes separatley or merged based on some value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

srinirao88
Starting Member

India
10 Posts

Posted - 03/12/2012 :  11:20:21  Show Profile  Reply with Quote
Hi,

So basically Service_num, Act1,Act2,Act3,Act4 are shown as columns whereas SR,Diag1,diag2,diag3,diag4 are not shown but it is merged into the same column of Service_num, Act1,Act2,Act3,Act4 which is not ideally what I am looking for....

Srinirao88
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2012 :  11:34:21  Show Profile  Reply with Quote
so do you mean this?

Create view _View1 AS
select [Service_num], [Act Tier1],[Act Tier2],[Act Tier3],[Act Tier4],
CAST(NULL AS varchar(20)) AS SR,
CAST(NULL AS nvarchar(20)) AS Diag Tier1],
CAST(NULL AS nvarchar(20)) AS [Diag Tier2],
CAST(NULL AS nvarchar(20)) AS [Diag Tier3],
CAST(NULL AS nvarchar(20)) AS [Diag Tier4]
from  dbo.Act$ 

UNION ALL

select NULL,NULL,NULL,NULL,NULL,Convert(varchar(20),[SR])[SR],[Diag Tier1],[Diag Tier2],[Diag Tier3],[Diag Tier4] from SR$


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000