Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[MrHaugen]

I have made a plugin that connects to a MS SQL server to download and store track data.
My problem is that when I use SQL commands like SELECT ....................FROM ............ WHERE TrackName = "F.O.D." it only returns THAT EXACT result. Is there any way of replacing things like .,'\()*-+/ with some sort wildcards in the SELECT statement?
The tracks might be called things like "Don't", "Dont" Don't." etc etc. It will make people get crap search result.
I also need a way to make "A", "The" and "An" optional in the beginning of artist name I guess. At least "The".

Is there any easy way to do this i Visual Basic, or with a little more advanced SQL queries?

[Mr ChriZ]

I have made a plugin that connects to a MS SQL server to download and store track data.
My problem is that when I use SQL

Thats quite impressive I only learnt how to do that recently myself!
Take a look at Like
http://msdn2.microsoft.com/en-us/library/aa933232(SQL.80).aspx

[MrHaugen]

Well. I've been playing with a lot of stuff in school the last 5 years. The problem is that I've got no real depth in this stuff. Development is not my stong side. SQL "LIKE" queries would help a little bit, but would also cause some problems.

I could split up track names with space as separator, and then use them in a LIKE statement.

The problem comes when you have track like "The Unforgiven" and "The Unforgiven 2" by Metallical.
My plugin will retrieve files automaticly, and therefore it can not afford to get two results from the same artist.
I think it would be a little complex to make a SQL query that searches for results, including and excluding every thing expect letters and numbers.

I thought of replacing given characters in the track name and rather run the SQL query several times. It might make the plugin alot slower, but at least it will be more accurate.

[Mr ChriZ]

If you're gonna use wild cards then you're going to have
the posability of ending up with more than one result....
Searches usually work by appending and prepending with the
'*' character I believe.

[zirum]

Just by taking a quick look, I would maybe try to remove all special chars (in both search string and field to search on), but I am not very sure how well that would work.

Do any of you by the way know what kind of db jrmc uses? Is it a properitary solution, or may it be accessed by other tools as well?

[Mr ChriZ]

It's a proprietry system.

[src666]

OK, first off UCASE() is your friend. So is REPLACE(). These are SQL functions, so please note that in a SQL call you need to use single quotes ' instead of double quotes "

UCASE("Song title") = "SONG TITLE"
REPLACE("The Song's Title", "'", "") = "The Songs Title"

With your examples, you would have to nest calls:

UCASE(REPLACE(REPLACE("Don't.", "'", ""), ".", "")) = "DONT"

You have to be VERY careful with "The" and "A" and the like. You would have to use multiple LEFT() tests - best done in your programming code, instead of your SQL call.

There is also LTRIM() and RTRIM(), which remove white space from the left/right of the string.

Again, most of this is best done in the program, not the SQL. But when you are having to search against uncertain data, it's hard to avoid.

Finally, and unfortunately, the #1 best way to handle bad tag data is to correct the tags on the media files, instead of trying to back your way into the correct result by kludging together a ton of string manipulation. It's tedious, but it provides a huge ease of use return on your investment.

[MrHaugen]

OK, first off UCASE() is your friend. So is REPLACE(). These are SQL functions, so please note that in a SQL call you need to use single quotes ' instead of double quotes "

UCASE("Song title") = "SONG TITLE"
REPLACE("The Song's Title", "'", "") = "The Songs Title"

With your examples, you would have to nest calls:

UCASE(REPLACE(REPLACE("Don't.", "'", ""), ".", "")) = "DONT"

That's EXATLY what I need. Works like a dream! The plan is to store all track names wihout .' " \ , . and things like that. Also making them all in uppercase. Will make better hits...

As for the THE, A & AN on the Artist names, I'm a bit more sceptical. Could be some confusion between different artists.

[src666]

Just a quick hint - use UCASE/etc. in your WHERE clasues, not in your INSERT's unless you aren't going to be displaying the information, or trying to match back to the original.

In other words, let your database say "Don't." if that's in the tag. But whenever you are trying to perform a match using WHERE, that's the place to put your UCASE/REPLACE/etc. logic.

The reason is that someday you may need to display the data, and users generally don't like to see "DONT EAT JENS COOKIES" instead of "Don't Eat Jen's Cookies", and they will often be confused if the output doesn't match the input, especially if the transformation rules are hidden in code.

As an alternative, you can store the "mangled" version in a separate field, so you will have both versions in the database, one for searching/etc. and one for display.

Finally, I _strongly_ recommend that you do not use text fields like album name or track name as your key fields. It will bite you later - I promise. Someday you are going to want to change an artist name (correct spelling, whatever) and then the whole house of cards is going to fall down. Use an arbitrary key, either an auto-incrementing INT field or a GUID as your primary key (AlbumID, TrackID, ArtistID, etc.). I absolutely cannot stress this enough. Never use names/addresses/descriptions/etc. as keys.

[MrHaugen]

Just a quick hint - use UCASE/etc. in your WHERE clasues, not in your INSERT's unless you aren't going to be displaying the information, or trying to match back to the original.

In other words, let your database say "Don't." if that's in the tag. But whenever you are trying to perform a match using WHERE, that's the place to put your UCASE/REPLACE/etc. logic.

The reason is that someday you may need to display the data, and users generally don't like to see "DONT EAT JENS COOKIES" instead of "Don't Eat Jen's Cookies", and they will often be confused if the output doesn't match the input, especially if the transformation rules are hidden in code.

As an alternative, you can store the "mangled" version in a separate field, so you will have both versions in the database, one for searching/etc. and one for display.

I'm probably never gonna show the database data directly through the code, but it's a good point. I'll probably rather use the replace and Ucase function in ant out of the database for comparison/searching.

Finally, I _strongly_ recommend that you do not use text fields like album name or track name as your key fields. It will bite you later - I promise. Someday you are going to want to change an artist name (correct spelling, whatever) and then the whole house of cards is going to fall down. Use an arbitrary key, either an auto-incrementing INT field or a GUID as your primary key (AlbumID, TrackID, ArtistID, etc.). I absolutely cannot stress this enough. Never use names/addresses/descriptions/etc. as keys.

I'm using auto incrementing fileds for ArtistNumber, TrackNumber and TrackElementNumber. Those are my PK's and FK's. I'd never use names..

Thanks alot for the input src666!

[KingSparta]

In VB6 Ucase$ executes faster than Ucase alone

the same goes for LTRIM$, RTRIM$, Trim$

etc...

[MrHaugen]

In VB6 Ucase$ executes faster than Ucase alone

the same goes for LTRIM$, RTRIM$, Trim$

etc...

It's probably the same for vb.net 2005. Works alright, just don't have enough data yet to check the difference though.
Thanks King

[src666]

Actually, the $ syntax has been dropped in the .NET environments. It may be supported (not sure), but just for backwards compatibility.

[KingSparta]

Actually, the $ syntax has been dropped in the .NET environments. It may be supported (not sure), but just for backwards compatibility.

I see