using Left( )to locate data or lack of data 1

[DebHanleyRI]

I either add or update a field based on whether or not data exists in that field.

Example: I am looking for the following "<<<Recondo Received 9/28/10>>>" in a text field

The field may look like this:
CoPay required <<<Recondo Received 9/28/10>>> no gado

Using the following fails if the "<<<" does not exist. It actually wipes out everything in the appointment note

SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote)-1)
SET @ApptNote2 = RIGHT(@AppointmentNote, CHARINDEX('>>>', REVERSE(@AppointmentNote)-1))
print @ApptNote1
print @ApptNote2

UPDATE rlogic.exams
SET appointmentNote = @ApptNote1 + @ApptNote2 + @recstate where accession_no = @accessionno





not all who wander are lost....

 

[gmmastros]

Your problem is here:

[tt][blue]SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote)-1)
SET @ApptNote2 = RIGHT(@AppointmentNote, CHARINDEX('>>>', REVERSE(@AppointmentNote)-1))[/blue][/tt]

When the search string does not exist, CharIndex returns 0. So you'll get...

[tt]SET @ApptNote1 = LEFT(@AppointmentNote, -1)[/tt]

You can correct for this by making sure charindex does not return a 0. How? Give it something to find, like this....

SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote [!]+ '<<<'[/!])-1)
SET @ApptNote2 = RIGHT(@AppointmentNote, CHARINDEX('>>>', [!]'>>>' +[/!] REVERSE(@AppointmentNote))-1)

You can test it like this:

Declare @AppointmentNote VarChar(8000)
Declare @ApptNote1 VarChar(8000)
Declare @ApptNote2 VarChar(8000)

--Set @AppointmentNote = 'CoPay required <<<Recondo Received 9/28/10>>> no gado'
Set @AppointmentNote = 'CoPay required no gado'

SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote + '<<<')-1)
SET @ApptNote2 = RIGHT(@AppointmentNote, CHARINDEX('>>>', '>>>' + REVERSE(@AppointmentNote))-1)
print @ApptNote1
print @ApptNote2

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DebHanleyRI]

works like a charm - THANK YOU!!!

not all who wander are lost....

 

[DebHanleyRI]

I am not getting back the value after the ">>>" and I will need that as well - I posted my test below:

Declare @AppointmentNote VarChar(8000)
Declare @ApptNote1 VarChar(8000)
Declare @ApptNote2 VarChar(8000)

Set @AppointmentNote = 'CoPay required no gado <<<<recondo received>>> test'

SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote + '<<<')-1)
SET @ApptNote2 = RIGHT(@AppointmentNote, CHARINDEX('>>>','>>>' + REVERSE(@AppointmentNote))-1)
print @ApptNote1
print @ApptNote2

I get back "CoPay required no gado" in @ApptNote1 but it does not return @ApptNote2 - am I missing something?

not all who wander are lost....

 

[gmmastros]

Sorry about that. I usually do a better job of testing the stuff I post.

Try this:

Declare @AppointmentNote VarChar(8000)
Declare @ApptNote1 VarChar(8000)
Declare @ApptNote2 VarChar(8000)

Set @AppointmentNote = 'CoPay required <<<Recondo Received 9/28/10>>> no gado'
--Set @AppointmentNote = 'CoPay required no gado'

SET @ApptNote1 = LEFT(@AppointmentNote, CHARINDEX('<<<', @AppointmentNote + '<<<')-1)
SET @ApptNote2 = Case When CharIndex('>>>', @AppointmentNote) > 0 
                      Then RIGHT(@AppointmentNote, CHARINDEX('>>>', REVERSE(@AppointmentNote))-1)
                      Else '' End

print @ApptNote1
print @ApptNote2

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DebHanleyRI]

you my friend are the BOMB - thank you!!!

not all who wander are lost....