Charindex returns false position in SQL Server 2017-CodePudding

I'm trying to get the charindex for multiple CHAR(10) characters in a string in SQL Server.

The first and second linebreaks are found alright, the 3rd one is a problem however.

CHARINDEX(CHAR(10),subadres) AS '1st linebreak',
CHARINDEX(CHAR(10),subadres,(CHARINDEX(CHAR(10),subadres) 1)) AS '2rd linebreak',
CHARINDEX(CHAR(10),subadres,(CHARINDEX(CHAR(10),subadres) 1) (CHARINDEX(CHAR(10),subadres))) AS '3rd linebreak',

In the example below, the second entry is okay. The first entry results in a false value for the 3rd linebreak (the || represent the CHAR(10) in my datasource).

I'm assuming the character on the right is causing this, but I can't find out how (and why).

Entry nr1:  Line 1||Line 2||Line 3||Line 4||
Entry nr2:  Line 1||Line 2||Line 3||Line4

I tried to trim the trailing CHAR(10) on the first line, this did not do anything however.

CodePudding user response：

If 2016 , consider the following JSON approach

Example

Declare @YourTable Table ([subaddrs] varchar(50))  Insert Into @YourTable Values 
 ('Line 1
Line 2
Line 3
Line 4
 ')
,('Line 1
Line 2
Line 3
Line4')
 
Select Pos1 = JSON_VALUE(JS,'$[0]')
      ,Pos2 = JSON_VALUE(JS,'$[1]')
      ,Pos3 = JSON_VALUE(JS,'$[2]')
      ,Pos4 = JSON_VALUE(JS,'$[3]')
      ,Pos5 = nullif(JSON_VALUE(JS,'$[4]'),'')  -- nullif() optional
 From  @YourTable A
 Cross Apply (values ('["' replace(
                           string_escape(
                           replace(
                           replace([subaddrs],char(13),'')
                           ,char(10),'||')
                           ,'json')
                           ,'||','","'
                           ) '"]'
                      ) ) C(JS)

Results

CodePudding user response：

Thanks for the replies. After checking the query again (and again), it looks like the nested Charindex was faulty.

The query below does the job:

CHARINDEX(CHAR(10),subadres)-1 AS '1st linebreak',
CHARINDEX(CHAR(10),subadres,(CHARINDEX(CHAR(10),subadres) 1)) AS '2nd linebreak',
CHARINDEX(CHAR(10),subadres,CHARINDEX(CHAR(10),subadres,(CHARINDEX(CHAR(10),subadres) 1)) 1) AS '3rd linebreak',