what's wrong with this wildcard character?

[Desolator144]

I'm using the wonderful AS400 DB2 for an SQL assignment (not by my choice!) and nobody in the class, the teacher included, can get the wildcard characters to work. If I remember correctly, it's a % for a * and an underscore for a single replacement character. I think I also have to use like instead of = when using a wildcard character but any way I write the statement, it won't run. For example, I know that "select * from team where teamnm like '%s%' will not return any results but it should cuz there's lots with an s in the name. I can't figure out what I'm missing but for an assignment we need to use them. I already used substr on one but another question is return all the records that end with a certain string so if anyone knows a test just the end trick or how to get wildcards to work, that'd be awesome.

[Ember]

And what is the the whole sentence?

[Desolator144]

it's:
select team.teamnm, teamldr from member, team where team.teamnm = member.teamnm and mbrtitle like '%Spec'
Now I did get it to work properly when I replaced '%Spec' with '%Spec%' but that's not correct because it has to be the last 4 letters in the value. I have absolutely no idea why no records are returned with the single % one because there are 2 that end with Spec and I bet I'll lose points if they say it could technically pick up 'Specifications Manager'

[si_the_geek]

Check the data.. the chances are that there is white space (or something else 'hidden') at the end of it.

[Desolator144]

lol dude, this is the AS400. You can't even use the mouse let alone just bring up a data table object to analyze :P select * IS the look at the table command. There's only a character datatype as far as I know and it always tacks on extra spaces but I don't remember it ever picking them up quite like that before.

[si_the_geek]

It doesn't matter what tools are available, you can still check what the field contains. eg:

select mbrtitle + '**' from tablename where mbrtitle like '%Spec%'

[szlamany]

Sounds like CHAR - with trailing white spaces - could be your issue...

Si gave a very nice technique for seeing "what's in a column" - by wrapping some delimiters around it in the SELECT.

AS400? Where in the world is this school anyway?

[Desolator144]

ugh, + doesn't work and neither does the concatonation character ||
wow, how are these AS400's still around? You can't even play pac man on them! But I am very very sure it tacks on extra spaces because it leaves room for however many characters the field specs are regardless of the data. So I guess technically none of them end in Spec like the question asks but I think it's good enough since at least I got results with the double one (though I still can't figure out why it doesn't work other times) I'm gonna kick that big, dumb IBM klunker on Monday.
oh and I'm in college in Wisconsin. I was PC and Midrange programming but I took Midrange Concepts (intro to the hell that is AS400) and dropped out of that cluster for PC Programming/Web Development. Apparently businesses everywhere still use those damn things which I can't imagine what that does to their efficiency, especially in data entry and training. I can't stand em though and RPG is just not gonna happen. At least the rest of the stuff is up to date. We're learning VS .NET 2.0 Speaking of that, you can come see me kick everyone's asses at the Detroit AITP programming competition

[Ember]

And this AS400 can have a syntax like:
"select team.teamnm, teamldr from member, team where ..."?

[Desolator144]

yes surprisingly :P

[si_the_geek]

That join syntax (with join conditions in the Where clause) used to be the standard, and is still supported by many/all DBMS's. Unfortunately the 'new' way isn't standardised across DBMS's - even MS Access and MS SQL Server use slightly different formatting!


It's annoying that the concatenation doesn't work.. the only alternative I can think of at the moment would be to do something like this (probably with an alternative for Len!):

select mbrtitle, Len(mbrtitle) from tablename where mbrtitle like '%Spec%'

..then you can count the characters displayed, and compare it. Not sure how much that helps tho.

Assuming you have a function like RTrim (or Trim) available, this may well work:

select mbrtitle from tablename where RTrim(mbrtitle) like '%Spec'

[Desolator144]

ya know, I bet there wasn't an rtrim but I did finish up the test today with that double %% statement and they damn well better mark it correct or they'll hear from my attorney!