SQL Picking up a string with two spaces in a row in a LIKE clause

Question

My database contains the following strings along with others that are similar

Old Type:  New Type: IRP User: dls0835
Old Type: BASE PLATE New Type: IRP User: ter2344

I am trying to not return the first string type but to still return the second string type.

Notice how there is no text after the "Old Type:" in the first string and that there is actually 2 spaces between that and the "New Type" word.

In the second string it has text after "Old Type:" but this text changes according to what the user picks.

Also the "New Type" and "User" can change as well.

I am trying to not Return any of the records where the "Old Type:" does not have any text after it.

I tried the following:

n.[text] NOT LIKE 'Old Type:  New Type: %'

This still returns the blank ones for some reason. I think it may have to due with there being two spaces between Old Type and New Type when Old Type is blank????

Thanks!

Answer 1

My initial suggestion is to modify your table layout to make the task easier.

Sometimes changing your angle of approach is the simplest solution.

Answer 2

How about:

WHERE CHARINDEX('Old Type:  New Type:', n.[text]) = 0

Answer 3

Try this:

where patindex('%New Type:%', n.text) > 12

Answer 4

here's an example of what i would do

declare @table table (String varchar(max))

insert into @table values ('Old Type:  New Type: IRP User: dls0835 ')
insert into @table values ('Old Type: BASE PLATE New Type: IRP User: tdl2921 ')


;with cte as
(
select
    SUBSTRING(string,CHARINDEX('Old Type: ',String)+10,CHARINDEX('New Type: ',String)-(CHARINDEX('Old Type: ',String)+10)) as OldType,
    SUBSTRING(string,CHARINDEX('New Type: ',String)+10,CHARINDEX('IRP User: ',String)-(CHARINDEX('New Type: ',String)+10)) as NewType,
    right(string,len(string)-(CHARINDEX('IRP User: ',string)+8)) as IRPUser
from @table
)
select
*
from cte
where OldType<>''