Solved

SQL query equivalent to ListLen()

Posted on 2006-07-20
23
856 Views
Last Modified: 2008-02-01
I need this quickly.  Find total list of words in the table row, "KEYWORDS". It's a comma delimited list.

<cfquery name="Query" datasource="Words">
     select area_id, title, keywords
     from areas
     where (len(keywords) < 80)
</cfquery>

That gives total length of combined elements in the list.  I need the total number of elements, that is, the total words in the list "KEYWORDS".  With Coldfusion I can use ListLen(). How should I edit the query above for an equivalent way to do this in SQL2000?
0
Comment
Question by:RollinNow
  • 11
  • 6
  • 5
  • +1
23 Comments
 
LVL 8

Expert Comment

by:raj_
ID: 17151039
CANT U DO A QUERY OF A QUERY AND GET IT???
<cfquery name="Query" datasource="Words">
     select area_id, title, keywords
     from areas
     where (len(keywords) < 80)
</cfquery>

<cfquery name="Query" dBTYPE="WordscNT">
     select area_id, title, keywords, LISTLEN(keywords)
     from Query
</cfquery>

must say ,interesting problem..
0
 

Author Comment

by:RollinNow
ID: 17151130
Hmm. I've never seen such an idea. I like it when that hapens. Most of the time.

I tried your suggestion, carefully, and got this error:

The 'DATASOURCE' attribute is required for all DBTYPES except QUERY and DYNAMIC

I assume that your dBTYPE="WordscNT" means to place the query name in front of "cNT" so that if
the query name was, say, JUNK, it would read:

<cfquery name="Query" dBTYPE="JunkcNT">

and that "from Query", means to also use the name of the actual Query? If the error is not obvious, could you explain what's going on? This may be interesting, but not half as much as your suggestion!

   - Georgia




0
 

Author Comment

by:RollinNow
ID: 17151534
I had really hoped to have this completed tonight, hense the 500 points.
So, can anyone else share in the points and please expain to me what RAJ means with the dBTYPE="WordscNT" in:

<cfquery name="Query" dBTYPE="WordscNT">
     select area_id, title, keywords, LISTLEN(keywords)
     from Query
</cfquery>

and why the error.

Or an alternative suggestion.


    - Georgia
0
 
LVL 4

Expert Comment

by:indu_mk
ID: 17152287
I don''t know ColdFusion.
But you can do like this in SQL 2000
declare @t int
set @t=0
select @t=@t+len(keywords) from query
print @t
0
 
LVL 8

Expert Comment

by:raj_
ID: 17153174
lol.. me stupid!


<cfquery name="MainQuery" datasource="Words">
     select area_id, title, keywords
     from areas
     where (len(keywords) < 80)
</cfquery>
-- for QoQ, dbtype should be "query"!!..
<cfquery name="UrQuery" dBTYPE="query">
     select area_id, title, keywords, LISTLEN(keywords)
     from MainQuery
</cfquery>
0
 
LVL 8

Expert Comment

by:raj_
ID: 17153826
also.. incase u want a sql solution

CREATE FUNCTION dbo.SplitMe2(@param VARCHAR(30))
RETURNS int
as
begin
--declare @param VARCHAR(30)
DECLARE @TempTable TABLE
(
      value varchar (150)
)
declare @rowCnt int
--set @param = 'raj,dave'
insert into @TempTable
SELECT SUBSTRING(',' + @param + ',', Numbers.Number + 1,
             CHARINDEX(',', ',' + @param + ',', Numbers.Number + 1)
             - Numbers.Number - 1) AS Value
        FROM Numbers
       WHERE Numbers.Number BETWEEN 1
         AND LEN(',' + @param + ',') - 1
         AND SUBSTRING(',' + @param + ',', Numbers.Number, 1) = ','
select @rowCnt=count(*) from @TempTable
return @rowCnt
end



this needs a numbers table..

SELECT TOP 100 Number = IDENTITY(INT, 1, 1)
  INTO Numbers
  FROM sysobjects s1, sysobjects s2
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17155917
Try it this way (assuming that Keywords is a varchar column data type):

select area_id, title, keywords,
         LEN(Keywords) - LEN(REPLACE(Keywords, ' ','') + 1 KeywordCount
from areas
where (len(keywords) < 80)
0
 

Author Comment

by:RollinNow
ID: 17156097
Acperkins,

Thanks for the help. Your idea seemed the easiest to try but I got this error:

Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'KeywordCount'.
SQL = "select area_id, title, keywords, LEN(keywords) - LEN(REPLACE(keywords, ' ','') + 1 KeywordCount
from areas where (len(keywords) < 80)"

I also tried "KeywordsCount", and other variations. Not sure what Replace gets me. All spaces have been trimmed out of the list.

Hopefully it's just a syntax error and you or someone will point out the problem with your suggestion, or what I might be doing wrong with it.

I'll try other suggestions now, if I can understand them.
0
 
LVL 8

Expert Comment

by:raj_
ID: 17156203
ok, once u have the function created..

<cfquery name="Query" datasource="Words">
     select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount
     from areas
     where (len(keywords) < 80)
</cfquery>
0
 

Author Comment

by:RollinNow
ID: 17156360
raj:

I was careful to use your suggestion exactly, being sure the datasource and table names were correct.
Your updated suggestion gave this error:

Query Manipulation Error Code = 0
Invalid SQL
SQL = "select area_id, title, keywords, LISTLEN(keywords) from MainQuery"
Data Source = ""

Your other SQL suggestion is far and away beyond my knowledge or ability. But I ran them anyway, was told by SQL that they ran correctly. I see that a type U and FN were created in table sysobjects. I don't even know what that table is about and have never looked at it so I'm afraid that sort of approach won't work for this female brain. So maybe we should stick to your other suggestion and see what the problem might be with it. Thanks for helping me, but I still need it as it's not working, or I'm not understanding the code you gave me. But I did use it exactly as you suggested.

  - Georgia
0
 

Author Comment

by:RollinNow
ID: 17156404
raj,

Your last post came as I was writing my last so I didn't see it. So, I tried your lastest query:

<cfquery name="Query" datasource="Words">
     select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount
     from areas
     where (len(keywords) < 80)
</cfquery>

Which I assume should be run by itself and got this:

ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.SplitMe2'.
SQL = "select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount from areas where (len(keywords) < 80)"

fyi:  "keywords" is stored in a varchar table.



0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17156414
>>Your idea seemed the easiest to try but I got this error:<<

You are right, I was missing a paranthesis, try it this way:

select area_id, title, keywords,
         LEN(Keywords) - LEN(REPLACE(Keywords, ' ','')) + 1 KeywordCount
from areas
where (len(keywords) < 80)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17156442
Assuming I understood your requirements: get the total number of keywords in each row and assuming that it is a varchar and the row look like this:
apple orange pear plum

That is one space between each keyword, than the concept becomes quite simple:  Get the length of the string and subtract the length of the string without the spaces (REPLACE() function).  Then the difference + 1 will be the number of keywords.
0
 

Author Comment

by:RollinNow
ID: 17156443
raj:

Just to be sure this was correct, here's a copy of the User Defined Function in the database:

CREATE FUNCTION dbo.SplitMe2(@param VARCHAR(30)) RETURNS int AS BEGIN DECLARE @TempTable TABLE ( value varchar (150) ) DECLARE @rowCnt int INSERT INTO @TempTable SELECT SUBSTRING(',' + @param + ',', Numbers.Number + 1, CHARINDEX(',', ',' + @param + ',', Numbers.Number + 1) - Numbers.Number - 1) AS Value FROM Numbers WHERE Numbers.Number BETWEEN 1 AND LEN(',' + @param + ',') - 1 AND SUBSTRING(',' + @param + ',', Numbers.Number, 1) = ',' SELECT @rowCnt=COUNT(*) FROM @TempTable RETURN @rowCnt END
0
 

Author Comment

by:RollinNow
ID: 17156470
acperkins:

Thanks for the clarification. The KEYWORD list looks like this: apple,orange,pear,plum

It is varchar, 512 bytes to cover fairly large lists. SQL2000.

If this modifies your suggestion, please let me know how, that is, with the query.

 - Georgia


0
 

Author Comment

by:RollinNow
ID: 17156531
acperkins:

I had missed your updated query suggestion and just tried it. No errors. But the count is wrong. That may be because there are spaces in the list, contrary to what I thought. I'll have to trim them and see what happens and get back to you. But just wanted to let you know so you didn't think I was totally stupid!
0
 

Author Comment

by:RollinNow
ID: 17156674
acperkins:

I still have a problem in the count and think it must be the remaining spaces. The reason is that the elements can have multiple words so, each separated by spaces, of course.

First, I made certain the keywords where trimmed. Once trimmed they look like this:

luana piovani,luana piovani nua,foto luana piovani,da foto luana nua piovani

Then I ran your query again. Notice that keywords can contain multiple words. The query result indicates this small keyword row has a keywordcount of 10, when it is actually just a count of 4.

So, how to do this just using the comma as the reference, preserve the spaces, and get the correct count? Sorry to make this so difficult.  At least your code is working toward what I need.

   - Georgia
0
 
LVL 8

Expert Comment

by:raj_
ID: 17156945
georgia..
1) did the query of query optin work (after my correction - regarding dbtype="query"
2) if not do this
   a) create the Numbers table ( its a simple tabke having values from 1 to 1000

SELECT TOP 100 Number = IDENTITY(INT, 1, 1)
  INTO Numbers
  FROM sysobjects s1, sysobjects s2

b) create the udf as mentioned above
c) in ur sql query
<cfquery name="Query" datasource="Words">
     select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount
     from areas
     where (len(keywords) < 80)
</cfquery>
0
 

Author Comment

by:RollinNow
ID: 17157426
raj,

> did the query of query optin work (after my correction - regarding dbtype="query"

No. This is what I got:

ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.SplitMe2'.
SQL = "select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount from areas where (len(keywords) < 80)"

> create the Numbers table ( its a simple tabke having values from 1 to 1000

So, I can create a the Numbers table then run it but ow should I create it? What Data Type, and length?

I'm not sure what you mean by "values" from 1 to 1000. I'm sorry I don't understand this more. I feel like I'm blindfolded, holding your hand, walking on a short road leading to the Grand Canyon.

I also don't know what udf means. I looked it up, have several different ideas, none clear so I'm not sure what you want me to do, in regard to udf. If you point me to it, I'll find that sucker and pounce on it!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 350 total points
ID: 17158084
>>It's a comma delimited list.<<
I overlooked this part.  I thought it was space delimited.  All you have to do is change the space to a comma in other words this:

select area_id, title, keywords,
         LEN(Keywords) - LEN(REPLACE(Keywords, ',','')) + 1 KeywordCount
from areas
where (len(keywords) < 80)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17158106
P.S. There should not be any need to remove spaces in the keywords.

Can you send me this: "da foto luana nua piovani"  :)
0
 
LVL 8

Assisted Solution

by:raj_
raj_ earned 150 total points
ID: 17158212
lol..
ok..
for the query of query there is no need for the UDF..
<cfquery name="MainQuery" datasource="Words">
     select area_id, title, keywords
     from areas
     where (len(keywords) < 80)
</cfquery>
-- for QoQ, dbtype should be "query"!!..
<cfquery name="UrQuery" dBTYPE="query">
     select area_id, title, keywords, LISTLEN(keywords)
     from MainQuery
</cfquery>



as regards the UDF.. its easy
first open query analyser and log into  ur database
1) run this script.. this will ceate the Numbers table for u..

SELECT TOP 100 Number = IDENTITY(INT, 1, 1)
  INTO Numbers
  FROM sysobjects s1, sysobjects s2


2) create the UDF. again, run this in query analyser

CREATE FUNCTION dbo.SplitMe2(@param VARCHAR(30))
RETURNS int
as
begin
--declare @param VARCHAR(30)
DECLARE @TempTable TABLE
(
      value varchar (150)
)
declare @rowCnt int
--set @param = 'raj,dave'
insert into @TempTable
SELECT SUBSTRING(',' + @param + ',', Numbers.Number + 1,
             CHARINDEX(',', ',' + @param + ',', Numbers.Number + 1)
             - Numbers.Number - 1) AS Value
        FROM Numbers
       WHERE Numbers.Number BETWEEN 1
         AND LEN(',' + @param + ',') - 1
         AND SUBSTRING(',' + @param + ',', Numbers.Number, 1) = ','
select @rowCnt=count(*) from @TempTable
return @rowCnt
end


3) now after doing both, run ur query in query analyser and test the result

select area_id, title, keywords, dbo.SplitMe2(keywords) wordCount
     from areas
     where (len(keywords) < 80)




0
 

Author Comment

by:RollinNow
ID: 17158814
Hmm. Looks like my final post wasn't saved. I used acperkins's solution and spit the points. Thanks to you both. My job is saved and at work no one knows I didn't figure it out myself.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now