Replace function not working on open parenthesis

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

Transact-SQL (2008)

Replace function not working on open parenthesis

Author

Topic

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-18 : 13:08:48

Hello!

I am working in a SQL based proprietary report design program. My end goal is to pull field data into a report, then export to a fixed length format file (CSV). I successfully replaced ',' '"' but when I try to include '(' in my replace the system thinks it is a new command and insists that I need a closed paren to save.

Working-without '(':
SQL{SELECT replace(replace(REF, ',', ''), '"', '') AS REF FROM HIST WHERE...

Not working:
SQL{select replace(replace(replace(REF,'(',''),'”',''),',(,'') AS REF FROM HIST WHERE ...

I also tried '(%)' - assuming there would be an end paren somewhere.

Thanks!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 13:13:15

you've not closed last braces. can you tell how your string will be and what you're trying to achieve from above

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

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-18 : 13:41:10

I understand the system is looking for a close parenthesis, but I placed single quotes around it to tell the system that it is text not a function. But it still insists.....sigh!

The text I am pulling may look like this:
Unit (1), cleaning by "Bob"

To export it to my CSV file I need it to translate to:
Unit 1 cleaning by Bob

Thanks V

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 13:50:24

[code]
see this example

select replace(replace(replace('Unit (1), cleaning by "Bob"','(',''),'"',''),'),','')

so in your case it would be
select replace(replace(replace(REF,'(',''),'"',''),'),','') from Hist where...
[/code]

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

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-18 : 14:04:44

Ah, so you are adding single quotes around the entire text reference. I will give it a go. Thanks V!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 14:07:36

no probs...you're wc

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

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-18 : 14:21:46

Well, Visakh16 - you ARE the man/woman! Thank you so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 04:07:32

quote:
Originally posted by Oneandonlyv

Well, Visakh16 - you ARE the man~~/woman~~! Thank you so much!

Welcome mate

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

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-22 : 16:45:52

Hello again.... ;)

So it appears that I also need to remove the apostrophes/single quote ( ' ) from my text. I can understand why it has an issue since this is key to the language. But what's a girl to do?!?!?!? HELP! THANK YOU!

When I add it to the query it returns the following error: Error -2147217908: 0: Command text was not set for the command object. (proc: rdoExecSql: )

This is my current query. It works when I replace the final REPLACE function (single quote) with any other character.

SQL{select replace(replace(replace(replace(replace(REF,'(',''),'"',''),')',''),',',''),''','') from Hist where ...

Thank you for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:06:22

[code]select replace(replace(replace(replace(replace(REF,'(',''),'"',''),')',''),',',''),'''','') from Hist where ...[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:13:45

also see this to understand how quotes work

http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

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

Kristen
Test

22859 Posts

Posted - 2011-08-23 : 02:19:49

FWIW when I do nested replaced I format them like this - as it makes it easy to add more (and balance the number of REPLACE with the parameters. To add more parameters just cut&paste a line - it includes commas etc


select 
	replace(replace(replace(replace(replace(
		REF
		,'(', '')	-- Remove (
		,'"', '')	-- Remove "
		,')', '')	-- Remove )
		,',', '')	-- Remove ,
		,'''', '')	-- Remove '
from Hist where ...

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-23 : 11:02:51

Thank you so much Visakh16! I wonder if you ever sleep?!!?
Great suggestion Kristen!! My eyes go blurry trying to decipher the coding.
Thank you both so much!

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-23 : 11:05:57

Oh, thanks for the link too!

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-23 : 11:19:19

I am sorry for the remedial question...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:30:30

quote:
Originally posted by Oneandonlyv

Thank you so much Visakh16! I wonder if you ever sleep?!!?
Great suggestion Kristen!! My eyes go blurry trying to decipher the coding.
Thank you both so much!

welcome
Yep...I do sleep though it doent appear so to you people

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

Oneandonlyv
Starting Member

13 Posts

Posted - 2011-08-23 : 15:39:08

Glad to know you are getting your rest! ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources