get rid of brackets. - 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

get rid of brackets.

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-22 : 14:57:11

(sr)

how do i get rid of the brackets without using any function?
i was reading performance tuning. it says its better not to use functions as it slows down the SP.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:00:23

But even built-in REPLACE is a function.
Are you referring to USER DEFINED FUNCTIONS?

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-22 : 15:03:31

yeah i know.
then how are we suppose to avoid it if we must use it.

is the article wrong or what?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:11:32

It depends on what the function does.
There are a lot of examples where UDFs are the only way to go in order to accomplish a task.

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-22 : 15:12:11

not UDF

im talking about replace, substring, left

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-22 : 15:23:50

Any data transformations performed in sql server will require the use of some built-in functions. The ones you mentioned above can be extremely efficient. Excpetions can be if you are using these functions as part of your JOIN or WHERE criteria. If you use a column (that is indexed) in an expression the optimizer may not use the index as part of the execution plan.

Be One with the Optimizer
TG

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-22 : 15:55:44

update table
set field = replace(field, '(', '')
where field in ('(sr)', '(III)')

update table
set field = replace(field, ')', '')
where field in ('(sr)', '(III)')

how can i get rid of both brackets using only 1 update.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 16:00:17

update table
set field = replace(replace(field, '(sr)', 'sr'), '(III)', 'III')
where field in ('(sr)', '(III)')

But this will be much better
update table
set field = 'sr'
where field = '(sr)'
update table
set field = 'III'
where field = '(III)'

TG
Master Smack Fu Yak Hacker

6065 Posts

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

I'm not sure your two updates will be "much better" than the single update with the nested replace. But here is another option that might give best of both:


update [table]
set field = case 
		when field = '(sr)' then 'sr'
		when field = '(III)' then 'III'
		end
where field in ('(sr)', '(III)')

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 16:18:48

Yeah, "much" was a very relative term there

Given that this is probably a once off update it really shouldn't matter?!

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-22 : 17:20:38

what about if i have a field with "Larry (JR)" and "larry ('III')"
i have the following code. will take any suggestion to make it faster.

update [table]
set field = case when field like '% (jr)' then replace(field, '(jr)', 'jr')
when field like '% (iii)' then replace(field, '(iii)', 'iii')
end
where field like '% (jr)'
or field like '% (iii)'

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 20:10:46

quote:
Originally posted by funketekun

what about if i have a field with "Larry (JR)" and "larry ('III')"
i have the following code. will take any suggestion to make it faster.

update [table]
set field = case when field like '% (jr)' then replace(field, '(jr)', 'jr')
when field like '% (iii)' then replace(field, '(iii)', 'iii')
end
where field like '% (jr)'
or field like '% (iii)'

In this case, the WHERE clause is very inefficient because it means SQL Server has to scan the entire table, so the speed of the replace function calls is pretty irrelevant.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-23 : 08:36:45

snSQL, how can i tune it up?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 08:40:04

UPDATE [Table] SET [Column] = REPLACE(REPLACE([Column], '(', ''), ')', '')
WHERE [Column] LIKE '%(%' OR [Column] LIKE '%)%'

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-23 : 09:07:42

peso,
i only want to remove the brackets that has jr, sr, ii, iii, iv inside.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 09:10:37

Tadaa!

UPDATE		[Table]
SET		[Column] = REPLACE(REPLACE([Column], '(', ''), ')', '')
WHERE		[Column] LIKE '%(jr)%'
		OR [Column] LIKE '%(sr)%'
		OR [Column] LIKE '%(ii)%'
		OR [Column] LIKE '%(iii)%'
		OR [Column] LIKE '%(iv)%'

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 09:12:00

Or more specific

UPDATE		[Table]
SET		[Column] = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Column], '(jr)', 'jr'), '(sr)', 'sr'), '(ii)', 'ii'), '(iii)', 'iii'), '(iv)', 'iv')
WHERE		[Column] LIKE '%(jr)%'
		OR [Column] LIKE '%(sr)%'
		OR [Column] LIKE '%(ii)%'
		OR [Column] LIKE '%(iii)%'
		OR [Column] LIKE '%(iv)%'

Peter Larsson
Helsingborg, Sweden

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-11-23 : 13:47:24

i just wonder,what is replace inside a replace called?

because it looks like derived table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 14:02:41

It's called effective writing.
A derived table returns a resultset of records.

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-23 : 14:16:53

quote:
Originally posted by funketekun

i just wonder,what is replace inside a replace called?

because it looks like derived table.

It's generally referred to as "nesting". I made mention of that in my second post on this thread.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources