 | |
#11
 
| |||
| |||
|
Re: Query??????? -
02-13-2004
, 07:15 AM
|
Nope Its not working, It results Null. PL help me to find out the correct way. ram DSL, London www.dslsoft.net "Rob Verschoor" <rob (AT) DO (DOT) NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in message news:<402b7bfe$0$70729$4a441750 (AT) news (DOT) euronet.nl>... "Adam H" <adam_NO_SPAMM_horan (AT) yahoo (DOT) com> wrote in message news:402b6ed1$0$28845$afc38c87 (AT) news (DOT) easynet.co.uk... Rob Verschoor wrote: Probably because you ran it in 12.5.0.3. As I mentioned, the code with str_replace() actually runs in 12.5.0.3, but the results are incorrect for the problem we're trying to solve here. The reason it works in 12.5.1 is that the NULL argument doesn't render the result NULL (as it does in 12.5.0.3) but actually removes the string from the source string (much like the stuff() function does). The comment in the 12.5.1 docs about the behaviour of NULL values hasn't been updated correspondingly (yet). As for the 'empty' string: there is no such concept in Transact-SQL. A string of '' (two single quotes) always evaluates to a single space -- this is documented, it's not a bug. There are some workarounds: see the bit about the 'empty string' in my ASE QuickRef guide. Anyway, here's a workaround for 12.5.0.3: replace each string by a string that's one character longer -- the total increase in length corresponds to the number of occurrences. This has the risk of overrunning the length of the string, which is why I don't like it. HTH, Rob V. I was running it in 12.5.1 - but I was being confused by the incorrect documentation, and my assumption that an empty string would be empty. Is this empty string issue, documented or not, a design choice by Sybase? Part of ANSI? Something every RDBMS does but i've just not seen? Adam (I'd missed the bit in your guide about the empty string - it's tucked down at the bottom of the page and wasn't obvious enough for me. Especially before coffee time.) It's a 'feature' of ASE, and it has always been like that (MS-SQL behaved identically until 6.0 and still does when it's in 6.x compatibilty mode). This behaviour not ANSI-compliant, and other DBMSs have usually implemented something different that isn't ANSI-compliant either (Oracle treats a string with a length of zero as NULL, for example). HTH, Rob ------------------------------------------------------------- Rob Verschoor Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0 and Replication Server 12.5 Author of "Tips, Tricks & Recipes for Sybase ASE" and "The Complete Sybase ASE Quick Reference Guide" Online orders accepted at http://www.sypron.nl/shop mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME http://www.sypron.nl Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands ------------------------------------------------------------- |
#12
| |||
| |||
|
|
Dear all, Please help me to do the char count in a query... Example: table-A --------- Name ---- rammohan ----------------------- I want to find no of 'a'-s in the field Name Name No of 'a's ---- ---------- rammohan 2 ------------------------------------------- |
I have used the following query...
create table #a(name varchar(20))
insert into #a values('rammohan')
declare @s varchar(10)
select @s = 'a'
select name,
(len(name) -
isnull(len(str_replace(name,@s,NULL)),0))/len(@s) "#occurrences"
from #a
The result I'm getting is 8 not 2 because str_replace is not working.
Ram DSL, London
www.dslsoft.net
"Rob Verschoor" <rob (AT) DO (DOT) NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote
Quote:
|
Without more informatio, it's all speculation... so please post the full details. Rob V. "Ram DSL" <reachram (AT) bharatmail (DOT) com> wrote in message news:f1565749.0402130454.16a1a4cf (AT) posting (DOT) google.com... Nope Its not working, It results Null. PL help me to find out the correct way. ram DSL, London www.dslsoft.net |
#13
| |||
| |||
|
|
I have used the following query... create table #a(name varchar(20)) insert into #a values('rammohan') declare @s varchar(10) select @s = 'a' select name, (len(name) - isnull(len(str_replace(name,@s,NULL)),0))/len(@s) "#occurrences" from #a The result I'm getting is 8 not 2 because str_replace is not working. |
And what is the result of 'select @@version' ?
Luc.
#14
| |||
| |||
|
|
Without more informatio, it's all speculation... so please post the full details. Rob V. "Ram DSL" <reachram (AT) bharatmail (DOT) com> wrote in message news:f1565749.0402130454.16a1a4cf (AT) posting (DOT) google.com... Nope Its not working, It results Null. PL help me to find out the correct way. ram DSL, London www.dslsoft.net "Rob Verschoor" <rob (AT) DO (DOT) NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in message news:<402b7bfe$0$70729$4a441750 (AT) news (DOT) euronet.nl>... "Adam H" <adam_NO_SPAMM_horan (AT) yahoo (DOT) com> wrote in message news:402b6ed1$0$28845$afc38c87 (AT) news (DOT) easynet.co.uk... Rob Verschoor wrote: Probably because you ran it in 12.5.0.3. As I mentioned, the code with str_replace() actually runs in 12.5.0.3, but the results are incorrect for the problem we're trying to solve here. The reason it works in 12.5.1 is that the NULL argument doesn't render the result NULL (as it does in 12.5.0.3) but actually removes the string from the source string (much like the stuff() function does). The comment in the 12.5.1 docs about the behaviour of NULL values hasn't been updated correspondingly (yet). As for the 'empty' string: there is no such concept in Transact-SQL. A string of '' (two single quotes) always evaluates to a single space -- this is documented, it's not a bug. There are some workarounds: see the bit about the 'empty string' in my ASE QuickRef guide. Anyway, here's a workaround for 12.5.0.3: replace each string by a string that's one character longer -- the total increase in length corresponds to the number of occurrences. This has the risk of overrunning the length of the string, which is why I don't like it. HTH, Rob V. I was running it in 12.5.1 - but I was being confused by the incorrect documentation, and my assumption that an empty string would be empty. Is this empty string issue, documented or not, a design choice by Sybase? Part of ANSI? Something every RDBMS does but i've just not seen? Adam (I'd missed the bit in your guide about the empty string - it's tucked down at the bottom of the page and wasn't obvious enough for me. Especially before coffee time.) It's a 'feature' of ASE, and it has always been like that (MS-SQL behaved identically until 6.0 and still does when it's in 6.x compatibilty mode). This behaviour not ANSI-compliant, and other DBMSs have usually implemented something different that isn't ANSI-compliant either (Oracle treats a string with a length of zero as NULL, for example). HTH, Rob ------------------------------------------------------------- Rob Verschoor Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0 and Replication Server 12.5 Author of "Tips, Tricks & Recipes for Sybase ASE" and "The Complete Sybase ASE Quick Reference Guide" Online orders accepted at http://www.sypron.nl/shop mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME http://www.sypron.nl Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands ------------------------------------------------------------- |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.