SEO

#1 (**permalink**) 02-29-2008, 06:18 PM

Hi
I have a character field, varchar(60), and I want to replace only a part
of it, a set substring which is always in the same set positions.
Does anyone have an Sql statement or algorithm/sp that could do that
with one call or run ?
Thanks
David

#2 (**permalink**) 02-29-2008, 06:18 PM

here is an example of SUBSTRING/REPLACE

CREATE TABLE #temp(stringRep VARCHAR(60))
INSERT INTO #temp(stringRep) VALUES('hazyCow')
INSERT INTO #temp(stringRep) VALUES('lazyCow')

DECLARE @startPos INT
SET @startPos = 2
UPDATE #temp SET stringRep = REPLACE(stringRep,SUBSTRING(stringRep, 2,
3),'zz')

SELECT * FROM #temp

DROP TABLE #temp

--
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp

"David Greenberg" <davidgr@iba.org.il> wrote in message
news:43E77029.9010301@iba.org.il...
> Hi
> I have a character field, varchar(60), and I want to replace only a part
> of it, a set substring which is always in the same set positions.
> Does anyone have an Sql statement or algorithm/sp that could do that
> with one call or run ?
> Thanks
> David
>

#3 (**permalink**) 02-29-2008, 06:19 PM

David Greenberg (davidgr@iba.org.il) writes:
> I have a character field, varchar(60), and I want to replace only a part
> of it, a set substring which is always in the same set positions.
> Does anyone have an Sql statement or algorithm/sp that could do that
> with one call or run ?

UPDATE tbl
SET col = substring(col, 1, 20) + 'newval' + substring(col, 26, 60)
FROM tbl
WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4 (**permalink**) 02-29-2008, 06:19 PM

On Mon, 06 Feb 2006 17:50:01 +0200, David Greenberg wrote:

>Hi
>I have a character field, varchar(60), and I want to replace only a part
>of it, a set substring which is always in the same set positions.
>Does anyone have an Sql statement or algorithm/sp that could do that
>with one call or run ?
>Thanks
>David

Hi David,

Look up the STUFF function in Books Online - this will do exactly what
you want.

--
Hugo Kornelis, SQL Server MVP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how do i replace a substring in sqlserver ?