Character replace? - 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

Character replace?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-11-14 : 14:58:09

Hi,

lets say i have a table and having some varchar columns. What i wanna do is, to change "ý" character with "I" character in those columns.

Can anyone help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 14:59:52

still haven't found Books Online?

update mytable set mycolumn = replace(mycolumn, 'ý', 'I')

Peter Larsson
Helsingborg, Sweden

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-11-14 : 15:05:46

thanks lots

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 15:10:14

To make the query more efficient, use

update mytable set mycolumn = replace(mycolumn, 'ý', 'I')
where charindex('ý', mycolumn) > 0

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-14 : 16:04:36

quote:
Originally posted by Peso

To make the query more efficient, use

update mytable set mycolumn = replace(mycolumn, 'ý', 'I')
where charindex('ý', mycolumn) > 0

Peter Larsson
Helsingborg, Sweden

How is that more effecient?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 16:22:47

Because only the rows actually contains character ý will get through the replace function.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-14 : 16:32:49

Still gonna cause a scan though

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 00:59:32

"Still gonna cause a scan though"

But the UPDATE (without WHERE) will physically update every row with identical data, won't it? Or is it smart enough to realize that nothing actually changed. Having said that it will HAVE to do the Update in order to fire any Update Triggers (if any), won't it?

"where charindex('ý', mycolumn) > 0"

Peso: Is that preferred to where mycolumn like '%ý%' ? (which might be cover-able by an index?)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 01:58:31

I find it difficult for LIKE '%ý%' to utilize an index.
But if possible, naturally that is the preferred way.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-15 : 09:13:21

quote:
Originally posted by Peso

I find it difficult for LIKE '%ý%' to utilize an index.
But if possible, naturally that is the preferred way.

Peter Larsson
Helsingborg, Sweden

Pete...can I call you Pete?

Either one is a stage 2 predicate, and will cause a scan. It can't use an index if you think about it...how could it.

Kristen.....oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 15:47:28

"It can't use an index if you think about it...how could it."


CREATE TABLE dbo.tempStringTest
(
	MyID int IDENTITY(1,1) NOT NULL,
	MyString varchar(10) NOT NULL,
	PRIMARY KEY
	(
		MyID
	)
)
GO

CREATE INDEX ixCoverString ON dbo.tempStringTest
(
	MyString,
	MyID
)
GO

INSERT INTO dbo.tempStringTest(MyString)
SELECT	'AAAyAAA' UNION ALL
SELECT	'BBByBBB' UNION ALL
SELECT	'CCCyCCC' 
GO

SELECT	MyID
FROM	dbo.tempStringTest
WHERE	MyString like '%y%'

/**
StmtText
-----------------------
  |--Index Scan(OBJECT:([MyDB].[dbo].[tempStringTest].[ixCoverString]),  
     WHERE:(like([tempStringTest].[MyString], '%y%', NULL)))
**/

Kristen

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 15:48:57

P.S. Its SCANning the index, but on a table with lots of columns that has to be better than scanning the table, no? - well, assuming you only want the ID and not all the other columns, but even then JOINing the results of this back to the table will the do a Lookup on the IDs in the PK/Clustered index, I reckon

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 16:07:17

As you said, if the index covers the query (which it does here) then all the data is available from fewer pages in the index than in the table, so the optimizer chooses the index for that reason.

The point then, is that it is using an index for reasons that have nothing to do with the predicate - the predicate is not optimizable and isn't affecting the index choice.

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 16:22:02

Indeed, and even a function call will still "use" the Index:


SELECT	MyID
FROM	dbo.tempStringTest
WHERE	charindex('y', MyString) > 0

StmtText
---------------------
  |--Index Scan(OBJECT:([DEBUG_TEST].[dbo].[tempStringTest].[ixCoverString]),  
     WHERE:(charindex('y', [tempStringTest].[MyString], NULL)>0))

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-15 : 16:32:56

so a scan is not a scan?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 16:42:19

Try it without the WHERE, it will still use the index - it's just decided that the index is the most efficient "store" for it to retrieve the data from - indexes are not only used for seeks.

The point is that if it reads the data from the table, then it has to read all those pages that are ful of other data that our query doesn't need so it reads the data from the index because the index holds the data we want without all the other columns we don't want - therefore we read far fewer total pages.

In this particular example the entire table is in the index because the table only has two columns but it's still a good choice - no worse than scanning the table, and if the table had lots of columns, then much better than scanning the table.

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 01:21:54

Yeah I know all that! I was just answering Bretts point about LIKE '%something' being able to use a covering index.

"the entire table is in the index because the table only has two columns"

SQL will usually behave the same even with more columns in the table

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources