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 |
|
mdelgado
Posting Yak Master
141 Posts |
 Posted - 2003-02-21 : 09:27:51
|
| good morning. Can someone please tell me how to count the number of occurences in a string?For example, I would like to know how many times the letter E comes up in the following sentence:"The Fox The Fox" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-21 : 09:49:27
|
| select datalength('The Fox The Fox') - datalength(replace('The Fox The Fox','e',''))Jay White{0} |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-21 : 10:16:09
|
quote:
select datalength('The Fox The Fox') - datalength(replace('The Fox The Fox','e',''))Jay White{0}
Nice, but shouldn't you be using LEN? If we are talking VARCHAR there would be no difference. But in case of CHAR you might not get the correct answer.declare @string varchar(20)select @string = 'The Fox The Fox'select datalength(@string) - datalength(replace(@string,'e',''))result: 2declare @string char(20)select @string = 'The Fox The Fox'select datalength(@string) - datalength(replace(@string,'e',''))result: 7 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-21 : 10:52:45
|
| Haven't checked it, would datalength cause problems for NVARCHARs as well? (2 bytes per character ?)- Jeff |
|
|
|
ojn.
Starting Member
10 Posts |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-21 : 14:57:25
|
quote:
Haven't checked it, would datalength cause problems for NVARCHARs as well? (2 bytes per character ?)- Jeff
DATALENGTH will show you the space allocated for the variable as declared, while LEN will show you the actual space the variable occuppies. n/varchar are dynamic. when you declare varchar(30) no space is allocated until you asign a value to the variable, and then it will use just the space needed according to the length of the vairable.Chars are fixed. when declareing char(30) the whole space is allocated, therefore LEN and DATALENGTH will always be of the same value. declare @string Nvarchar(20)select @string = 'The Fox The Fox'select datalength(@string) - datalength(replace(@string,'e',''))results 4 (since you have 2 times the byes). same with Nvarchar(30).This is something to consider when you have to choose between char or varchar column. If column has a more or less fixed length, you can gain by using CHAR since processing a fixed length column will be faster. Otherwise you should consider using varchar to save space and reduce i/o reads. Bambola. |
|
|
|
|
|
|
|
|
count the number of occurences in a string.