Link to home
Start Free TrialLog in
Avatar of developingprogrammer
developingprogrammer

asked on

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!! = ))
Avatar of magento
magento

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?
Avatar of developingprogrammer

ASKER

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!!
hi PortletPaul! i'm covering only Access
it's also LEN( string_expression ) in Access
SizeMatters([field] < 4

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

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

(i.e. you won't be able to avoid doing exactly that)
haha mx i was thinking along the lines of "HowBigAmI()" = PP
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 = )
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
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?"
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?
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 :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
lol ..yeah.  IF ... only I had time ....
@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

https://www.experts-exchange.com/questions/28230107/how-to-know-if-function-is-Access-based-or-SQL-based.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!! = ))