On Monday, August 15, 2016 at 8:35:17 AM UTC-5, Mike Bayer wrote:
>
>
>
> On 08/15/2016 12:02 AM, r...@rosenfeld.to <javascript:> wrote:
> > Hi All,
> >
> > I'm not sure whether I've done something wrong, hit an unsupported
> > version of SQL Server, or what.
> >
> > I have a Person table in my SQL Server database that I'm reflecting via
> > `autoload`
> > |
> >
> > classPerson(Base):
> > __table__ =Table('Person',meta,autoload=True,autoload_with=engine)
> >
> > |
> >
> > The table has LastName and FirstName columns, but I want to search for
> > substrings in the concatenation of FirstName and LastName. The
> > following query works direct against the SQLServer
> > |
> >
> > SELECT *FROM dbo.PersonWHERE (FirstName+' '+LastName)LIKE '%ob Smi%'
> >
> > |
> >
> > Note that trying to use a `CONCAT` function returns the error.
> > |
> >
> > SELECT *FROM dbo.PersonWHERE concat(FirstName+' '+LastName)LIKE '%ob
> Smi%'
> > *[S00010][195]'concat'isnota recognized built-infunctionname.**
>
>
> string concatenation is available as an operator, the + sign will
> compile down to the concatenation operator itself (which is usually
> '||', I thought this was the case for SQL server also). assuming string
> concatenation operators still work on SQL server 2012 I'd forego the
> direct use of "concat"
>
> matches = Person.query.filter(Person.FirstName + ' ' ' +
> Person.LastName).like(name_substring)).all()
>
>
>
Thanks. That did it.
>
> > *
> >
> > |
> > It seems that CONCAT was added in SQLServer 2012 and later:
> > https://msdn.microsoft.com/en-us/library/hh231515.aspx
> >
> > Trying to replicate this query via SQLAlchemy, the following executes,
> > but generates invalid SQL including a call to CONCAT with the error
> below.
> > |
> >
> > matches =Person.query.filter(db.func.concat(Person.FirstName,'
> > ',Person.LastName).like(name_substring)).all()
> >
> > |
> >
> > |
> >
> sqlalchemy.exc.ProgrammingError:(pyodbc.ProgrammingError)('42000','[42000]
> > [FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)
> > (SQLExecDirectW)')[SQL:'SELECT Person_1.[LastName] AS
> > [dbo_Person_LastName], Person_1.[FirstName] AS
> > [dbo_Person_FirstName]\nFROM dbo.Person AS Person_1 \nWHERE
> > concat(Person_1.[FirstName], ?, Person_1.[LastName]) LIKE
> > ?'][parameters:(' ','ob Smi')]
> > |
> >
> >
> > I don't see any mention in the documentation of how to indicate the SQL
> > Server version that queries should be compiled for.
> >
> > Any suggestions?
> >
> > Thanks,
> > Rob
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
