CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user380432
user380432

Reputation: 4769

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

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!

Upvotes: 2

Views: 440

Answers (4)

DForck42
DForck42

Reputation: 20327

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<>''

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

Try this:

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

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

How about:

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

Upvotes: 2

Shaun Mason
Shaun Mason

Reputation: 797

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

Sometimes changing your angle of approach is the simplest solution.

Upvotes: 0

Related Questions