CODEWITHSUNDEEP
t-sqlsubstringcharindex
adinger
adinger

Reputation: 11

SQL Selecting character string between multiple character non constant

My SUBSTRING/CHARINDEX query broke a few nights ago and I'm struggling to understand why. I'm trying to select the characters (Date) between "Appt. Date:" and "Appt. Time:" in comments like:

Status: Future Appointment  Appt. Date:12/14/18  Appt. Time:9:30am
Status: Obtaining Results  Appt. Date:10/05/18  Appt. Time:4:00
Status:   Appt. Date:8/28/2018  Appt. Time: 9:15am

Using this query:

select ltrim(
            rtrim(
                SUBSTRING([Order Comments], 
                      CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:'), 
                      CHARINDEX('Appt. Time',[Order Comments], CHARINDEX('Appt. Date:', [Order Comments])) - 
                          (CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:'))
                 )
            )
        ) as 'OrderApptDate'
FROM [HIRS_Tools].[dbo].[OMT_BOD]
WHERE [Order Comments] like 'status:%'

SQL error:

Invalid Length Parameter passed to the LEFT or SUBSTRING function.

Should I be taking a different approach to selecting the date characters, or is there simply a problem with my current query? Any help would be greatly appreciated.

Upvotes: 0

Views: 98

Answers (1)

adinger
adinger

Reputation: 11

First, many thanks for the posts everyone. Even though none of them gave me the exact answer, they all gave me a piece to answer the puzzle. Especially, HABO and 3N1GM4!

Eventually I discovered the Appt Date and Appt Time fields are not always exactly the same. There are combinations of Appt. Date: & Appt. Time, Appt. Date - & Appt. Time, Appt Date & Appt. Time...All discovered from HABO's field length expression. So, I added a case statement adjusting for each [Appt Date/Appt Time] combo field length. This resolved everything and gave me enough leverage to have my users go clean up the remaining records and implement a better data entry work flow.

The new query looks like this:

select
 case 
  when CHARINDEX('Appt. Time',[Order Comments], CHARINDEX('Appt. Date:', [Order Comments])) - (CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:')) <15
  and [Type]='Procedure' AND [Order Comments] like 'status:%' and [Order Comments] like '%Appt. Time%'
       then ltrim(rtrim(SUBSTRING([Order Comments], CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:'), CHARINDEX('Appt. Time:',[Order Comments], CHARINDEX('Appt. Date:', [Order Comments])) - (CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:')))))
  when CHARINDEX('Appt. Time',[Order Comments], CHARINDEX('Appt. Date:', [Order Comments])) - (CHARINDEX('Appt. Date:', [Order Comments]) + LEN('Appt. Date:')) <15
  and [Type]='Procedure' AND [Order Comments] like 'status:%' and [Order Comments] like '%Appt Time%'
       then ltrim(rtrim(SUBSTRING([Order Comments], CHARINDEX('Appt Date:', [Order Comments]) + LEN('Appt Date:'), CHARINDEX('Appt Time:',[Order Comments], CHARINDEX('Appt Date:', [Order Comments])) - (CHARINDEX('Appt Date:', [Order Comments]) + LEN('Appt Date:')))))
  when (CHARINDEX('Appt. Time',[Order Comments], CHARINDEX('Appt. Date -', [Order Comments])) - (CHARINDEX('Appt. Date -', [Order Comments]) + LEN('Appt. Date -'))) <15
  and [Type]='Procedure' AND [Order Comments] like 'status:%' and [Order Comments] like '%Appt. Date -%'
       then ltrim(rtrim(SUBSTRING([Order Comments], CHARINDEX('Appt. Date -', [Order Comments]) + LEN('Appt. Date -'), CHARINDEX('Appt. Time:',[Order Comments], CHARINDEX('Appt. Date -', [Order Comments])) - (CHARINDEX('Appt. Date -', [Order Comments]) + LEN('Appt. Date -')))))
  when (CHARINDEX('Appt. Time',[Order Comments], CHARINDEX('Appt. Date ', [Order Comments])) - (CHARINDEX('Appt. Date ', [Order Comments]) + LEN('Appt. Date '))) <15
  and [Type]='Procedure' AND [Order Comments] like 'status:%' and [Order Comments] like '%Appt. Date %'
       then ltrim(rtrim(SUBSTRING([Order Comments], CHARINDEX('Appt. Date ', [Order Comments]) + LEN('Appt. Date '), CHARINDEX('Appt. Time:',[Order Comments], CHARINDEX('Appt. Date ', [Order Comments])) - (CHARINDEX('Appt. Date ', [Order Comments]) + LEN('Appt. Date ')))))
       else [Date - Due] end as 'OrderApptDate'

FROM [HIRS_Tools].[dbo].[OMT_BOD]
WHERE ([Order Comments] like 'status:%')

Again, thank you for the help and I hope someone finds this useful in the future.

Upvotes: 1

Related Questions