| Author |
Topic |
|
ConradK
Posting Yak Master
140 Posts |
 Posted - 2009-11-24 : 11:29:11
|
| So, I have a data set. Some is like:one, two, threeand others are like:one, two, three, four, fiveand others still are more like:one, two, three, four, five, sixwhat I want to do, is build a rule for only rows with 5 items are taken. So, if code worked the way my mind does, I would right something like thiscase if COUNT(dataset, ',', '5') then AS "Keywords" else AS "needs fixing" endbut it doesn't work that way of course.I am very new to SQL and would appreciate some help. Just to summarize, I want the code to include data that has 5 commas, and exclude anything =/= 5 commas. Any help, greatly appreciated. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-11-24 : 11:37:56
|
| select * from mytablewhere len(replace(mycol,",","")) = len(mycol) -5 |
 |
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2009-11-24 : 11:44:34
|
| cool, thanks for the quick reply. Whats that code doing then? I'm familiar with replace, and vaguely know that len limits the number of characters? and I'm not sure how that does what I'm looking for. I realy don't know much about SQL though. Just wondering if you can help explain it to me so I can use its functions in the future.To elaborate a little more, we have a column with keywords, many of which carry =/= 5 keywords. In order to do something else i've figured out, they need to have exactly 5 keywords, so I'm looking for it to take only products with 5 keywords so I can work with that data. Also, using the same function, I will make another list of products which are non-compliant and edit them to make them compliant.Does this make sense? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-24 : 13:16:16
|
If you are looking for 5-word-columns then you should look only for 4 commas.Andrews solution (but better you use single quotes...) does this:count the length (characters) of the column (maybe 26)replace all commas by nothing and count again.If there are now 5 characters less than before - bingo!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2009-11-24 : 13:32:28
|
| That is really cool. I totally get that. Thanks alot guys. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-11-25 : 03:54:38
|
| C'mon ConradK - experiment!!! The best learning process in the world!select mycol, len(mycol), replace(mycol,",",""), len(replace(mycol,",",""))from mytablewhere len(replace(mycol,",","")) = len(mycol) -5 |
|
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2009-12-04 : 17:33:30
|
| okay, so i understand what i'm trying to do, i think i just have a syntax error at this point...case when len(replace(pm.metatag_keywords, ',', ''))=len(pm.metatag_keywords)-4 then pm.METATAG_Keywords else ', , , ,' end AS "SearchTerm1,SearchTerm2,SearchTerm3,SearchTerm4,SearchTerm5"it keeps saying"Argument data type text is invalid for argument 1 of replace function."i've tried restructuring it like eight different ways... |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-12-05 : 10:48:24
|
Your pm.metatag_keywords is of data type text.replace() isn't working with data type text.You can use convert(varchar(max),pm.metatag_keywords) instead of pm.metatag_keywords.And what is that???quote:
else ', , , ,' end AS "SearchTerm1,SearchTerm2,SearchTerm3,SearchTerm4,SearchTerm5"
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-07 : 02:23:31
|
quote:
Originally posted by webfred
Your pm.metatag_keywords is of data type text.replace() isn't working with data type text.You can use convert(varchar(max),pm.metatag_keywords) instead of pm.metatag_keywords.And what is that???quote:
else ', , , ,' end AS "SearchTerm1,SearchTerm2,SearchTerm3,SearchTerm4,SearchTerm5"
No, you're never too old to Yak'n'Roll if you're too young to die.
Only if the version is 2005 or more  MadhivananFailing to plan is Planning to fail |
|
|
|
ConradK
Posting Yak Master
140 Posts |
Posted - 2009-12-07 : 10:37:23
|
| THAT "else ', , , ,' end AS "SearchTerm1,SearchTerm2,SearchTerm3,SearchTerm4,SearchTerm5"" is how i worked around the comma problem I had, although now I'm running into non compliant data entries, which I'm trying to filter out with the strip of code I have presented.I'm eternally thankfull for your advising me! However I don't quite understand the solution.If my code becomes case when len(replace(convert(varchar(max),pm.metatag_keywords), ',', ''))=len(pm.metatag_keywords)-4 then pm.METATAG_Keywords else ', , , ,' end AS "SearchTerm1,SearchTerm2,SearchTerm3,SearchTerm4,SearchTerm5"that will work? |
|
|
|
|
|
|
Counter? What?