Solution figured out.
It was a line feed character
‘’‘SELECT
Key1 as UserID, content,
CHARINDEX(’ ‘,Content) AS Spaces,
‘’ + SUBSTRING(Content, 10,1) + '’ as TenthChar,
ASCII(SUBSTRING(Content, 10,1)) as ASCTenthChar
FROM [EpicorProd].[Ice].[XXXDef] xd
WHERE xd.TypeCode = ‘MainMenuHistory’
AND xd.key1 in (‘baumgarta2’)
‘’’
Apologies if I leave out some important piece for posting. This is my first one.
I found this code to get to the user recent menu list.
‘’‘select TypeCode, Key1, Content from Ice.XXXDef where TypeCode = ‘MainMenuHistory’ order by Key1’‘’
The poster noted that content seems to store a SPACE separated list of MENU IDs.
It does appear to be a SPACE, I have checked it with ASCII() function. parsed it notepad++, then as a literal string using XML parsing, however trying to XML parse from the database table it does not parse. I have also tried CTE and same problem. It doesn’t parse. STRING_SPLIT() is not available in my sql server.
I have successfully used CTE to parse the ‘~’ delimited SecCode Entry list in [Ice].[Security]
This doesn’t work
‘’’
SELECT
A.UserID,
Split.a.value(‘.’, ‘NVARCHAR(100)’) AS Item
FROM
(
SELECT
Key1 as UserID,
CAST (‘’ + REPLACE(REPLACE(Content, CHAR(160), ’ ‘), ’ ‘, ‘’) + ‘’ AS XML) AS MenuList
FROM [EpicorProd].[Ice].[XXXDef] xd
WHERE xd.TypeCode = ‘MainMenuHistory’
AND xd.key1 in (‘baumgarta2’)
) AS A
CROSS APPLY MenuList.nodes (’/M’) AS Split(a)
‘’’
This does work
‘’'DECLARE @MyTable TABLE(
ID INT,
DelimitedField VARCHAR(MAX)
)
INSERT INTO @MyTable
VALUES (1, ‘TCRGO3500 CRGO3000 OMGO3007 CRGO6100 OMMT1110 OMGO3076 UDCP0127 SWORKB UDQTRK OMGO3006 OMGO3015 OMGO’), (2, ‘itemA itemB itemC’)
SELECT
A.ID,
Split.a.value(‘.’, ‘VARCHAR(100)’) AS Item
FROM
(
SELECT
ID,
CAST (‘’ + REPLACE(DelimitedField, ’ ‘, ‘’) + ‘’ AS XML) AS Data
FROM @MyTable
) AS A
CROSS APPLY Data.nodes (’/M’) AS Split(a)
‘’’
I used ChatGPT to try to find the issue, but no luck. any help will be appreciated. I’ll share my final query with this group when I succeed.