April 19, 2005 at 3:32 pm
Is there any SQL function that would handle counting the number of occurences of a character within the same column. I am trying to see how many tab characters I have in a column.
April 19, 2005 at 3:36 pm
Use Replace to replace the CHAR(9) tab character to "" empty and subtract the diff in length
April 19, 2005 at 11:12 pm
Yes, good suggestion John.
Select ..., [NumTabs] = (Len([*Column*]) - Len(Replace([*Column*],CHAR(9),""))),... from [*Table*]
Where [*Column*] is the column you want to count the number of tabs in, [*Table*] is the source table, ... is a column list for other columns being seleced, and [NumTabs] is the alias for the expression.
Or something similar...This won't work for Text or NText Data types, at least I don't think it will.
April 20, 2005 at 7:08 am
Adding to John's cool suggestion, you might want to package this into a re-usable user-defined function that you can include wherever you need. Something like:
CREATE FUNCTION dbo.fnStringCount ( @inStr varchar(8000), @inFind char(1) )
RETURNS int
AS
BEGIN
DECLARE @Tmp varchar(8000)
DECLARE @cnt int
SET @Tmp = REPLACE(@inStr, @inFind, '')
SET @cnt = LEN(@inStr) - LEN(@tmp)
RETURN @cnt
END
Then you can include this in your other SQL, like SELECT dbo.fnStringCount(MyColumn, CHAR(9)) AS TabCount
Steve
April 20, 2005 at 7:40 am
Depending on which string data type you use, have a look here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=153641
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply