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.
| Author |
Topic |
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
 Posted - 2004-10-18 : 21:43:00
|
| Is there a built in function in SQL that will evaluate if the field'slen is zero? and replace it with correspond value?example: LenIsZero(field, replaceWith)declare @AidaLou as charset @AidaLou = ''select LenIsZero(@AidaLou, 'The new value') as AidaLouThe result of this querry is ...AidaLou-------------------------------------The new valueany function of this? or i have to make my own function?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-18 : 22:08:54
|
| select case when len(@AidaLou) =0 then 'The new value' end as AidaLou--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-18 : 22:23:41
|
| Thanks rrb...Yes! i can use the case statement and others...But my question is? Is there any built in function just like the evaluation of the ISNULL function that willreplace the the value, if it's len is equal to zero to aa specified value?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-18 : 22:28:13
|
| sorry - not that I'm aware of.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-18 : 22:29:13
|
| its OK rrb...I appreciate your response to the post.Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-10-18 : 22:49:12
|
| my pleasure- wish I was more help--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-18 : 23:02:01
|
| What's wrong with using the CASE expression? If you had to, you could use the following too:IsNull(NullIf('', @aidalou), 'The New Value')It's likely this will evaluate no faster than rrb's version. This might be faster:CASE WHEN @aidalou='' THEN 'The New Value' ELSE @aidalou ENDBecause the len function is not called, but it's not likely to be measurable. Internally, IsNull() and NullIf() are subtle variations of, or outright conversions to, equivalent CASE expressions anyway. |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-18 : 23:15:02
|
quote:
Originally posted by robvolk
What's wrong with using the CASE expression? If you had to, you could use the following too:IsNull(NullIf('', @aidalou), 'The New Value')It's likely this will evaluate no faster than rrb's version. This might be faster:CASE WHEN @aidalou='' THEN 'The New Value' ELSE @aidalou ENDBecause the len function is not called, but it's not likely to be measurable. Internally, IsNull() and NullIf() are subtle variations of, or outright conversions to, equivalent CASE expressions anyway.
Thanks robvolk!There' nothing wrong with it, actually im just wondering if theres a functionof it? Yes youre right that ...CASE WHEN @aidalou='' THEN 'The New Value' ELSE @aidalou ENDis a much faster querry.I just want to know if theres a like of function of it?  Thnx again...Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
|
|
|
|
|
Function LenIsZero?