SQL equivalent of len() function

hey guys,

i want to use len([field])<=4 in my sql query but i don't want to use Access functions. what is the SQL equivalent for len? thanks guys!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

magentoCommented:
0
PortletPaulfreelancerCommented:
SQL Server TSQL also uses: LEN ( string_expression )
see: http://technet.microsoft.com/en-us/library/ms190329(v=sql.105).aspx

Oracle uses LENGTH()
and has some others as well:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions076.htm

What databases are you trying to cover?
0
developingprogrammerAuthor Commented:
thanks magneto! but i'm using Access SQL and length() gives me undefined function length() in expression.

what can i use in Access SQL? thanks!!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

developingprogrammerAuthor Commented:
hi PortletPaul! i'm covering only Access
0
PortletPaulfreelancerCommented:
it's also LEN( string_expression ) in Access
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
SizeMatters([field] < 4

OK ... I don't think there is an Access SQL equivalent for Len() ...

mx
0
PortletPaulfreelancerCommented:
:^) tricky question then if you "don't want to use Access functions"

(i.e. you won't be able to avoid doing exactly that)
0
developingprogrammerAuthor Commented:
haha mx i was thinking along the lines of "HowBigAmI()" = PP
0
Gustav BrockCIOCommented:
Len() is native to Access SQL, so the equivalent VBA function will not be called.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
spoiler ... lol.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The better question would be:

"What are the native Access SQL functions?"

mx
0
developingprogrammerAuthor Commented:
whao cool thanks guys!!

guys goes Access use T-SQL? so if i read the documentation for T-SQL does everything apply to Access? if i'm not wrong from what i know T-SQL and Access-SQL are slightly different

boag2000 shared with me this page for T-SQL functions http://technet.microsoft.com/en-us/library/ms174318.aspx

is there one like that for Access SQL functions?
0
PortletPaulfreelancerCommented:
Does Access use T-SQL = no
there are some syntax that is the same, but a good deal syntax that is different
(and the dbms 'engines' differ as well)

Here is a list at Microsoft - there are probably others for different versions
Access 2007 Functions (by category) an alphabetic list is also provided.

Here's an alternative site for Access functions etc. (techonthenet.com)
0
developingprogrammerAuthor Commented:
Thanks PortletPaul!!

So sorry if I was unclear in my question, but I'm looking for a list of Access SQL functions. The VBA functions list I can get using object browser = )
0
PortletPaulfreelancerCommented:
I believe you have convinced yourself that all Access functions are necessarily VBA, that isn't true. If you can use the function directly in the query, it isn't the VBA function that is being executed.

for LEN(), take note of the image at:
http://www.techonthenet.com/access/functions/string/len.php
that's calling LEN() via Access's sql
0
PortletPaulfreelancerCommented:
btw: internally Access might be calling the same code - I don't know if this is true or not and have never concerned myself with such knowledge.
I'm only looking at it from the perspective of:
 "can it be used directly in a query?"
0
developingprogrammerAuthor Commented:
Thanks PortletPaul! Hrmm an Access UDF can be called in the same way so I don't think that's the definer of an SQL function = P

I guess I'm coming from a purist point of view trying to write everything in only SQL so that it will be faster and from a training standpoint I learn too. Iif can be an SQL or Access function - if called in a query will use the SQL version cause SQL will use it's native functions first I believe.

I'm trying hard to be a purist but the lack of SQL functions documentation is stifling me!!

I remember Christian (Bitsqueezer) sayin once that "consider those people who use Access" - meaning real programmers will use SQL Server for almost 99.99% of scenarios. Maybe that's why Microsoft didnt bother teaching us how to optimise queries in Access. You think so?
0
PortletPaulfreelancerCommented:
There are many syntax difference between Access and SQL Server, and SQL Server has many differences to Oracle which has many differences to MySQL which has....

you get the idea

Yes, a UDF can be called like LEN() but it won't be listed amongst the lists I suggested. I also think you are reaching for a purity level that Access itself cannot provide.

Just look at the parentheses that Access insists on using for Joins that every other SQL db I know of does not need.

IIF() by the way is  not in all T-SQL versions as I understand it. (btw: I don't use IIF in SQL Server anyway, I prefer the much more pure SQL: CASE ... END syntax).

{+ edit}
oh, and UDFs exist in other dbms's too :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
@PortletPaul ...
Maybe I can help explain were all this is coming from.
Take a look at Allen's article - mainly the first two items ....

http://allenbrowne.com/QueryPerfIssue.html

This is what DP is getting at ....

mx
0
PortletPaulfreelancerCommented:
thanks, got it now, looks like a gap for an article here mx :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
lol ..yeah.  IF ... only I had time ....
0
developingprogrammerAuthor Commented:
@PortletPaul ...
Maybe I can help explain were all this is coming from.
Take a look at Allen's article - mainly the first two items ....

http://allenbrowne.com/QueryPerfIssue.html

This is what DP is getting at ....

mx

EXACTLY!! yayy!! = ))) thanks for understanding me mx!! i read through almost the whole of Allen Browne's site and omg what a task!! haha, but at least after going through everything i'm much much more assured of my understanding - kinda like leveling up haha = )

i should have quoted Allen Browne's article right from the start!! looks like i need to take up communication lessons to reach your concise level mx haha = ))

I also think you are reaching for a purity level that Access itself cannot provide.


woo hoooo!!!! this was the response i was looking for to confirm whether i can determine whether a function is a VBA only, Access SQL only or VBA and Access SQL function.

in my other question where boag2000 and angelIII was helping me, i reached this conclusion (below) and am pending their approval on it = P

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28230107.html
ok guys so let me try and sum everything up.

1) http://www.techonthenet.com/access/functions/ 
VBA and Access SQL functions - but it doesn't tell us if a function is only VBA, only Access SQL, or both.

2) http://technet.microsoft.com/en-us/library/ms174318.aspx
T-SQL function - this is USUALLY similar to Access SQL functions but not always

3) there is no object browser for Access SQL and also no Access SQL specific documentation.

Conclusion: there is no way we can tell definitively whether a function in Access is a VBA only function, SQL only function, or both VBA and SQL.

is this conclusion correct?

apart from Allen Browne's articles, a big part of why i'm in this purity search is also because of Christian's (Bitsqueezer) article which he shared with me - but it's in german and google translate can't translate everything, BUT i learnt a lot from it!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.