May 12, 2008 at 8:12 am
I have legacy data (I did not do it, it's about 10 years before i came to the company) where the only way to identify the row is by the column "ContestName", the problem is that the values of "ContestName" are all different, but similar...
Example:
DanceContest1
DanceContest2
DanceContest3
DanceContest4
SongContest1
SongContest2
ContestGuitar1
ContestGuitar2
ContestGuitar3
since there are hundreds of values I am trying to find a way to do a "Select Similar(Contestname)"
to output into a dropdown box or something so I can then do a SELECT LIKE with the "similar" truncated value
I have not been able to locate such function, but since I am a neophyte compared to most of you I figured it doesnt hurt to ask where to look. or if you have any ideas
Thanks in advance!
May 12, 2008 at 8:29 am
What constitutes SIMILAR?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 12, 2008 at 8:39 am
Well out of those examples I would say:
DanceContest
SongContest
ContestGuitar
would be the values i would like returned..
Granted if the only way to do it is by several characters (say the first or last 10) then so be it
I would just then to a like('DanceConte%') afterwards?
May 12, 2008 at 8:42 am
DECLARE@Sample TABLE (Info VARCHAR(100))
INSERT@Sample
SELECT'DanceContest1' UNION ALL
SELECT'DanceContest2' UNION ALL
SELECT'DanceContest3' UNION ALL
SELECT'DanceContest4' UNION ALL
SELECT'SongContest1' UNION ALL
SELECT'SongContest2' UNION ALL
SELECT'ContestGuitar1' UNION ALL
SELECT'ContestGuitar2' UNION ALL
SELECT'ContestGuitar3'
SELECT DISTINCTLEFT(Info, PATINDEX('%[0-9]%', Info) - 1)
FROM@Sample
ORDER BY1
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 8:45 am
Peso beat me to it. 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 12, 2008 at 8:47 am
Once again forgive the newb in me...
But the SELECT 'whatever' UNION ALL part...
Is that a necessary or were you using it as an example?
.. that data was just an example, the actualy tale has about 30 thousdand rows most with different (but SIMILAR) "contestName' values
May 12, 2008 at 8:53 am
No, the first part is only to mimic your environment.
We don't have access to your data.
This is the part you need.
SELECT DISTINCT LEFT(YourColHere, PATINDEX('%[0-9]%', YourColHere) - 1)
FROM YourTableHere
ORDER BY 1
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 9:02 am
You might also look into CONTAINSTABLE in BOL. It's a little more complicated, but I'd be willing to bet more flexibile and faster.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 12, 2008 at 9:05 am
Thank you guys, I owe you a beer
May 12, 2008 at 9:08 am
...and also look at CONTAINS.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 12, 2008 at 2:58 pm
Just remember that CONTAINS, CONTAINSTABLE, FREETEXT, and FREETEXTTABLE all require that Full Text Indexing is enabled and configured for the tables you're querying. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply