July 26, 2011 at 1:23 am
how to count number of characters in a string in sql query?
July 26, 2011 at 2:21 am
SELECT LEN('How long is this?')
This would return 17.
That help?
July 26, 2011 at 2:30 am
What datatype is the string?
DECLARE @Variable_Char CHAR(30), @Variable_Varchar VARCHAR(30)
SET @Variable_Char = 'String to measure '
SET @Variable_Varchar = 'String to measure '
SELECT LEN(@Variable_Char), LEN(@Variable_Varchar), DATALENGTH(@Variable_Char), DATALENGTH(@Variable_Varchar)
SELECT LEN(REPLACE(@Variable_Varchar, ' ', '')), LEN(REPLACE(@Variable_Char, ' ', ''))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 26, 2011 at 2:37 am
well actlly sorry for not explaining that properly i mean # of characters like fro instance the string is :--
SQLSERVER
result sholuld be like :--
S-2
Q-1
L-1
E-2
R-2
V-1
string is nvarchar..
July 26, 2011 at 4:59 am
DECLARE @WordToCheck VARCHAR(30) = 'SQL SERVER'
SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM (SELECT TOP 30 n = ROW_NUMBER() OVER(ORDER BY [Name]) FROM sys.columns) n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 26, 2011 at 5:11 am
July 26, 2011 at 6:55 am
ChrisM@Work (7/26/2011)
That's slick, and definitely going in my toolbox. I never would have guessed that could be done with set-based code.
July 26, 2011 at 6:59 am
pdanes2 (7/26/2011)
ChrisM@Work (7/26/2011)
That's slick, and definitely going in my toolbox. I never would have guessed that could be done with set-based code.
Thanks! Must admit though, it's borrowed from the teachings of Jeff Moden 🙂 Thanks Jeff.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 26, 2011 at 10:48 am
I would have to agree that it is very slick.
I was trying to think of doing it with out having to rely on sys.columns, but I can not think of a way.
Cool solution, made me think. :hehe:
July 26, 2011 at 10:56 pm
thnksss for the help 🙂
April 26, 2012 at 5:15 am
jwbart06 (7/26/2011)
I would have to agree that it is very slick.I was trying to think of doing it with out having to rely on sys.columns, but I can not think of a way.
Cool solution, made me think. :hehe:
April 26, 2012 at 5:41 am
ChrisM@Work (7/26/2011)
DECLARE @WordToCheck VARCHAR(30) = 'SQL SERVER'SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM (SELECT TOP 30 n = ROW_NUMBER() OVER(ORDER BY [Name]) FROM sys.columns) n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
This is SLICK!! Mr. Chris 🙂
April 26, 2012 at 8:00 am
jwbart06 (7/26/2011)
I would have to agree that it is very slick.I was trying to think of doing it with out having to rely on sys.columns, but I can not think of a way.
Cool solution, made me think. :hehe:
Use a tally table. The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM (SELECT TOP 300 n = ROW_NUMBER() OVER(ORDER BY N) FROM Tally) n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2012 at 7:54 pm
You gotta love the recursive CTE version of this solution, even though it doesn't perform as well as the tally table version.
CREATE TABLE #t (strings VARCHAR(100));
-- Count the unique characters
;WITH Parser (strs, rest, Num) AS (
SELECT SUBSTRING(strings,1,1), SUBSTRING(strings,2,LEN(strings)), 1
FROM #t UNION ALL
SELECT SUBSTRING(rest, 1, 1), SUBSTRING(rest, 2, LEN(rest)), 1
FROM Parser WHERE LEN(rest) > 0
)
SELECT strs, COUNT(Num) AS Count
FROM Parser
GROUP BY strs
DROP TABLE #t
Recursive CTEs! You gotta love their black little hearts even though they're perplexing!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 27, 2012 at 11:27 am
Sean Lange (4/26/2012)
jwbart06 (7/26/2011)
I would have to agree that it is very slick.I was trying to think of doing it with out having to rely on sys.columns, but I can not think of a way.
Cool solution, made me think. :hehe:
Use a tally table. The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM (SELECT TOP 300 n = ROW_NUMBER() OVER(ORDER BY N) FROM Tally) n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
you dont need the sub select to the tally table. in the original query where the sub select is from sys.columns it is creating the tally table on the fly. if you have a tally table in your database it would just be the following:
SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM Tally n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
why row_number a table of numbers??
i also ran the following on my million GUID table and it came out in 30 seconds (roughly). the recursive cte after 5 min had still not come back (is there any supprise there)
SELECT @letter = Letter, @count = COUNT(*)
FROM(
SELECT Letter = SUBSTRING(col, n.n, 1)
FROM Random
CROSS JOIN Tally n
WHERE n.n <= LEN(col)
) d
WHERE Letter <> ' '
GROUP BY Letter
ORDER BY Letter
----===== Tally Table =====-----
SQL Server Execution Times:
CPU time = 30872 ms, elapsed time = 31546 ms.
----===== Recursive CTE =====-----
Query was cancelled by user.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply