topic Re: Where clause for '\n' in Python Questions

Where clause for '\n'

JoeBorgione — Sat, 11 Dec 2021 23:44:12 GMT

ArcGIS 10.5.1 Python 2.7, enterprise geodatabse, SQL server back end

Using an arcpy.da.SearchCursor() how might I set up a where statement that looks for '\n' newline special character? Here is one of my iterations that does not work:

fields = ['NOTES']
where = '{} like %\n%'.format(fields[0])
with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields,where) as cursor:
...     for row in cursor:
...         print row[0]
...         
Runtime error 
Traceback (most recent call last):
  File "<string>", line 2, in <module>
RuntimeError: Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'.] [pweng.SLCOEN.BacteriaBatch]
‍‍‍‍‍‍‍‍‍‍

I've tried a few different iterations in the 'Select by Attributes' dialog in ArcMap, but haven't come across on that will return any records. I had one record selected that I know has '\n' in the field, and it shows up nicely:

with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor:
...     for row in cursor:
...         print row
...         
(u'Intensive\nFB-7:18',)

'''and'''

with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor:
...     for row in cursor:
...         print row[0]
...         
Intensive
FB-7:18
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This stems from a request associated with this post: https://community.esri.com/message/829382-re-when-a-return-is-used-in-a-text-field-in-survey123-the-subsequent-multi-lin…

Re: Where clause for '\n'

JoshuaBixby — Tue, 05 Feb 2019 00:39:40 GMT

Different languages use different characters for new lines and carriage returns. How to check my data in SQL Server have carriage return and line feed? - Stack Overflow

Re: Where clause for '\n'

JoeBorgione — Tue, 05 Feb 2019 01:11:26 GMT

Cool. Thanks. I'll give it a go in the morning!

Joshua Bixby‌

Re: Where clause for '\n'

DanPatterson_Retired — Tue, 05 Feb 2019 02:55:44 GMT

Newline - Wikipedia

some on languages etc and some conversions

thankfully there is only one None

Re: Where clause for '\n'

JoeBorgione — Tue, 05 Feb 2019 13:38:12 GMT

LOL...

I've got a feeling this is going to be a challenge; the data is an enterprise geodatabase service edited in Survey 123 on an Apple iPad.... hahahahaha!

Re: Where clause for '\n'

TedKowal — Tue, 05 Feb 2019 15:25:42 GMT

hmmm ..... Those are the reasons I love MS Assess

Re: Where clause for '\n'

TedKowal — Sat, 11 Dec 2021 23:44:15 GMT

oops forgot to add SQL SEVER SQL:

SELECT *
FROM TheTable
WHERE CHARINDEX(CHAR(10), ColumnOfInterest) > 0‍‍‍

Re: Where clause for '\n'

JoeBorgione — Sat, 11 Dec 2021 23:44:17 GMT

I think I cracked this nut: instead of worrying about a 'where' clause per se, I just plow through the records with an update cursor and look for the problems with that:

import arcpy
arcpy.env.workspace = r'J:\WaterQuality\test_tables.gdb'
table = "BacteriaBatch"
fields = ['OBJECTID', 'NOTES']           
with arcpy.da.UpdateCursor(table,fields) as updateRows:
    for updateRow in updateRows:
        if updateRow[1]== None:
            pass
        elif "\n" in updateRow[1]:
            updateRow[1] = updateRow[1].replace("\n","-")
            updateRows.updateRow(updateRow)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Where clause for '\n'

DanPatterson_Retired — Tue, 12 Feb 2019 01:20:53 GMT

better solution... step-wise... it has 'wise' in it

Re: Where clause for '\n'

JoeBorgione — Tue, 12 Feb 2019 12:25:40 GMT

In the years of my mis-spent youth, I worked as a diesel truck mechanic. That experience taught me to approach problem solving pragmatically. It also taught me that sometimes you just gotta use a bigger hammer....