Where clause for '\n'

697
9
02-04-2019 03:05 PM
JoeBorgione
MVP Esteemed Contributor

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-... 

That should just about do it....
0 Kudos
9 Replies
JoshuaBixby
MVP Esteemed Contributor

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 

JoeBorgione
MVP Esteemed Contributor

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

Joshua Bixby

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Newline - Wikipedia 

some on languages etc and some conversions

thankfully there is only one None

JoeBorgione
MVP Esteemed Contributor

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!

That should just about do it....
0 Kudos
TedKowal
Regular Contributor II

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

0 Kudos
TedKowal
Regular Contributor II

oops forgot to add SQL SEVER SQL:  

SELECT *
FROM TheTable
WHERE CHARINDEX(CHAR(10), ColumnOfInterest) > 0
0 Kudos
JoeBorgione
MVP Esteemed Contributor

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)‍‍‍‍‍‍‍‍‍‍‍
That should just about do it....
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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....  

That should just about do it....
0 Kudos