To find the count of a particular character inside a string
-
Dear All,
I need to find the number of "~" present in a string. Is there any inbuilt function to do the same?? if not, can you please help me to achieving that?
Pramod
-
The issue here is: I dont want to replace the original string. The second part is that i have some thousands of strings to be compared so, assigning the string to a temp variable gets out of Question. and even if i put the strings into a temp table and then use the logic below, then how do i compare them with the ones that is present in the main table. Can anything be done regarding the same?
Pramod
-
The issue here is: I dont want to replace the original string
I am not replacing the Original string. I have used Len() function and in that Function I have replaced '~' with ''. Original String is in its original state.
Declare @v-2 varchar(100)
Declare @i int
Set @v-2 = '12~12~~1234~13~~12~3~~1'
Select @i = Len(@v) - Len(Replace(@v,'~',''))
Select @i,@v
-
My Bad.
You are absolutely rt. Thanks for that... once again...
Pramod
-
Atif Sheikh (10/30/2009)
Glad it helped you.You need to be careful with this method:
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = Len(@v) - Len(Replace(@v,'~',''))
Select @i,@v
...apparently there are three ~ characters in that string :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (10/30/2009)
Atif Sheikh (10/30/2009)
Glad it helped you.You need to be careful with this method:
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = Len(@v) - Len(Replace(@v,'~',''))
Select @i,@v
...apparently there are three ~ characters in that string :laugh:
Yup, but there's only on in this one
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = datalength(@v) - datalength(Replace(@v,'~',''))
Select @i,@v
Don't know if there's any shortcoming of using Datalength instead of Len 😉
-
Ian Scarlett (10/30/2009)
Don't know if there's any shortcoming of using Datalength instead of Len 😉Sort of. You need to be careful to account for unicode. Here's a fun example:
Declare @v-2 varchar(100)
Declare @i int
Set @v-2 = '1234 ~'
Select @i = datalength(@v) - datalength(Replace(@v,'~',N''))
Select @i,@v
(There are -5 squiggles in the string)
Change varchar(100) to nvarchar(100) for a more serious look at the issue.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Ian Scarlett (10/30/2009)
Paul White (10/30/2009)
Atif Sheikh (10/30/2009)
Glad it helped you.You need to be careful with this method:
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = Len(@v) - Len(Replace(@v,'~',''))
Select @i,@v
...apparently there are three ~ characters in that string :laugh:
Yup, but there's only on in this one
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = datalength(@v) - datalength(Replace(@v,'~',''))
Select @i,@v
Don't know if there's any shortcoming of using Datalength instead of Len 😉
Doesn't work with nvarchars (out by a factor of 2)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
So the real answer is... Don't use Len, use Datalength, and don't use nVarchar or Unicode:-D
-
Ian Scarlett (10/30/2009)
So the real answer is... Don't use Len, use Datalength, and don't use nVarchar or Unicode:-D:laugh:
Seriously, I'm just saying one needs to be careful. I suppose a unicode-input function that always divided by two would be ok. Finding the number of occurrences of a character in string using T-SQL hasn't been a big requirement in my life so far...!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
this seems to work perfectly if one remembers that for unicode
1 character = 2bytes
and datalength counts bytes
so every two bytes = 1 character
-
Sorry, being fast on the draw.....
small modifications required is
When using dataLength() on unicode, devide by two to get the number of Characters
-
Thanks for pointing out. But the issue is still handleable with Len() function and without using nvarchar.
Declare @v-2 varchar(100)
Declare @i int
Set @v-2 = ' ~ 1234 ~ ~ '
Select @i = Len(Replace(@v,' ','?')) - Len(Replace(Replace(@v,' ','?'),'~',''))
Select @i,@v,Len(@v)
I have replaced ' ' with a special character which OP can check as invalid input. And I dont think the character I used ('?') to replace ' ' is used as data input.
I think this solves the issue.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply