Help required - 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

General SQL Server Forums

New to SQL Server Programming

Help required

Author

Topic

srinirao88
Starting Member

10 Posts

Posted - 2012-03-10 : 19:29:12

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)

17689 Posts

Posted - 2012-03-10 : 20:35:50

use REPLACE() twice


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

KH
[spoiler]Time is always against us[/spoiler]

srinirao88
Starting Member

10 Posts

Posted - 2012-03-11 : 09:22:52

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-11 : 09:47:26

[code]
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
[/code]

KH
[spoiler]Time is always against us[/spoiler]

srinirao88
Starting Member

10 Posts

Posted - 2012-03-11 : 11:12:28

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-11 : 11:50:25

[code]from Activity[/code]
is your table name "Activity" ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 11:56:16

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/

srinirao88
Starting Member

10 Posts

Posted - 2012-03-11 : 12:09:36

@ 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-11 : 22:06:22

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
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-11 : 22:10:33

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
[spoiler]Time is always against us[/spoiler]

srinirao88
Starting Member

10 Posts

Posted - 2012-03-12 : 00:43:46

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 [CODE][/CODE] it seems like it is not working here or I am not following the instructions...

Looking forward for your reply.

Srinirao88

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 00:46:49

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 [CODE][/CODE] 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/

srinirao88
Starting Member

10 Posts

Posted - 2012-03-12 : 02:46:22

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 02:46:29

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
[spoiler]Time is always against us[/spoiler]

srinirao88
Starting Member

10 Posts

Posted - 2012-03-12 : 02:52:27

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 04:09:52

then you have to convert SR to string explicitly

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 04:11:32

quote:
Originally posted by khtan

then you have to convert SR to string explicitly

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

KH
[spoiler]Time is always against us[/spoiler]

KH
[spoiler]Time is always against us[/spoiler]

srinirao88
Starting Member

10 Posts

Posted - 2012-03-12 : 06:32:03

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:
[CODE]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$
[/CODE]

Srinirao88

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:02:39

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/

srinirao88
Starting Member

10 Posts

Posted - 2012-03-12 : 11:20:21

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 11:34:21

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/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources