Replace() function in Transact-SQL 
Author Message
 Replace() function in Transact-SQL

I would like to create UDF that would do the same as Replace()
function in Visual Basic does:

sTemp = Replace('My string FFF','F','B')

SO sTemp should equal to 'My string BBB' when done.  Has anyone
created anything similar - meaning take an expression, and replace
desired string with another string that you need.

I looked into string manipulation functions and there is not one like
this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
string manip. group with possibly the loop to get things done the way
I need them.

Does anyone have this already?

Tigger



Sun, 11 Jul 2004 06:06:48 GMT
 Replace() function in Transact-SQL

Maybe I'm stating the obvious here, but how about using T-SQL's REPLACE()
function?




www.aspfaq.com

Quote:
> I would like to create UDF that would do the same as Replace()
> function in Visual Basic does:

> sTemp = Replace('My string FFF','F','B')

> SO sTemp should equal to 'My string BBB' when done.  Has anyone
> created anything similar - meaning take an expression, and replace
> desired string with another string that you need.

> I looked into string manipulation functions and there is not one like
> this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
> string manip. group with possibly the loop to get things done the way
> I need them.

> Does anyone have this already?

> Tigger



Sun, 11 Jul 2004 06:09:32 GMT
 Replace() function in Transact-SQL
REPLACE() is built in to SQL7 and higher.
Read about it within Books Online

--
Keith, SQL Server MVP


Quote:
> I would like to create UDF that would do the same as Replace()
> function in Visual Basic does:

> sTemp = Replace('My string FFF','F','B')

> SO sTemp should equal to 'My string BBB' when done.  Has anyone
> created anything similar - meaning take an expression, and replace
> desired string with another string that you need.

> I looked into string manipulation functions and there is not one like
> this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
> string manip. group with possibly the loop to get things done the way
> I need them.

> Does anyone have this already?

> Tigger



Sun, 11 Jul 2004 06:12:43 GMT
 Replace() function in Transact-SQL
you have replace() in T-Sql

Vasant


Quote:
> I would like to create UDF that would do the same as Replace()
> function in Visual Basic does:

> sTemp = Replace('My string FFF','F','B')

> SO sTemp should equal to 'My string BBB' when done.  Has anyone
> created anything similar - meaning take an expression, and replace
> desired string with another string that you need.

> I looked into string manipulation functions and there is not one like
> this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
> string manip. group with possibly the loop to get things done the way
> I need them.

> Does anyone have this already?

> Tigger



Sun, 11 Jul 2004 06:18:38 GMT
 Replace() function in Transact-SQL
Right - should have looked at BOL first before being too quick on
posting the Q heheheh

thanks  

Quote:

> you have replace() in T-Sql

> Vasant



> > I would like to create UDF that would do the same as Replace()
> > function in Visual Basic does:

> > sTemp = Replace('My string FFF','F','B')

> > SO sTemp should equal to 'My string BBB' when done.  Has anyone
> > created anything similar - meaning take an expression, and replace
> > desired string with another string that you need.

> > I looked into string manipulation functions and there is not one like
> > this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
> > string manip. group with possibly the loop to get things done the way
> > I need them.

> > Does anyone have this already?

> > Tigger



Sun, 11 Jul 2004 12:04:47 GMT
 Replace() function in Transact-SQL
I have been testing the Replace() function and it seems to limit the output
to around 250 characters. I had hoped to use Replace to take a field value
that contained something like:
==Value in varchar(8000) field================
    The following should
be a formated list
-Item One
-Item Two
-Item Three
==================
and return HTML like
==Preferred output for HTML================
&nbsp;&nbsp;&nbsp;&nbsp;The following should <br>be a formated list
<li>Item One
<li>Item Two
<li>Item Three
==================

My intention is to allow users to enter several thousand characters in a web
form and then store this in a table. The Select'd output that would be
displayed in a browser would replace CHAR(10)+'-' with '<li>'. Other
replacements such as &nbsp; would also be implemented.

Thanks
Duane


Quote:
> Right - should have looked at BOL first before being too quick on
> posting the Q heheheh

> thanks




Quote:
> > you have replace() in T-Sql

> > Vasant



> > > I would like to create UDF that would do the same as Replace()
> > > function in Visual Basic does:

> > > sTemp = Replace('My string FFF','F','B')

> > > SO sTemp should equal to 'My string BBB' when done.  Has anyone
> > > created anything similar - meaning take an expression, and replace
> > > desired string with another string that you need.

> > > I looked into string manipulation functions and there is not one like
> > > this exactly - but you have to use SUBSTRING, STUFF, CHARINDEX and the
> > > string manip. group with possibly the loop to get things done the way
> > > I need them.

> > > Does anyone have this already?

> > > Tigger



Mon, 12 Jul 2004 00:17:02 GMT
 Replace() function in Transact-SQL

Quote:
> I have been testing the Replace() function and it seems to limit the
output
> to around 250 characters.

That's likely the output you see in Query Analyzer, which is an option you
can set in the tool.  REPLACE() certainly works on strings up to 8000
characters, from at least 7.0; I use that functionality in production code.

www.aspfaq.com



Mon, 12 Jul 2004 00:20:09 GMT
 Replace() function in Transact-SQL
nbn (Nothing But Net)

Thanks
Duane



Quote:
> > I have been testing the Replace() function and it seems to limit the
> output
> > to around 250 characters.

> That's likely the output you see in Query Analyzer, which is an option you
> can set in the tool.  REPLACE() certainly works on strings up to 8000
> characters, from at least 7.0; I use that functionality in production
code.

> www.aspfaq.com



Mon, 12 Jul 2004 00:57:31 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. replacing Where in Transact SQL

2. Access SQL -> Transact SQL String Functions

3. Transact SQL Functions

4. String Functions in Transact SQL

5. Transact SQL Aggregate Function for String

6. using transact-sql functions in vb 6.0

7. Calling Transact SQL Function

8. User defined functions in Transact SQL

9. Is there a lookup function in transact SQL?

10. Calling C-functions from transact SQL

11. Calls of C-functions from Transact SQL

12. Are User Defined Functions possible in Transact SQL?


 
Powered by phpBB® Forum Software