Indentify second occurance of a character 3

[JeffMachine]

I have a field whose data looks like this:

003391
003391-01
003391-01-01
002498-02-01
004895-02
004895-02-01
005523-01

I would like to have a query that returns "Warranty" for lines 3,4 and 6 and nothing for the others.

Warranty items end in "-01" but only after the second dash. So lines 2 and 6 are not warranty items.

Thanks ...

 

[markros]

So, is this enough?

select case when right(Field,3) = '-01' and
len(Field) - len(replace(Field,'-','')) = 2 then 'Warranty' else '' end as IsWarranty ...

Field has 2 dashes and last portion of the field is '-01'

PluralSight Learning Library

 

[gmmastros]

There's a bunch of ways to do this. Shows here are 3 of them.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('003391')
Insert Into @Temp Values('003391-01')
Insert Into @Temp Values('003391-01-01')
Insert Into @Temp Values('002498-02-01')
Insert Into @Temp Values('004895-02')
Insert Into @Temp Values('004895-02-01')
Insert Into @Temp Values('005523-01')

-- method 1
Select *
From   @Temp
Where  Data Like '%-[0-9][0-9]-[0-9][0-9]'

-- method 2
Select	*
From    @Temp
Where   CharIndex('-', Data, CharIndex('-', Data)+1) > 0

-- method 3
Select	*
From    @Temp
Where   Len(Data) - Len(Replace(Data, '-','')) = 2

Method 1 looks for rows that end with (dash)(number)(number)(dash)(number)(number).

Method 2 actually find the location of the first dash, and then finds the location of the second dash. If the location of the 2nd dash is greater than 0, return the row.

Method 3 actually counts the number of dashes. By comparing the length of the data with the length of the data after dashes are removed, we can tell how many dashes there are. This method returns data if there are 2 dashes.

I suspect your actual data may be more complicated than the sample you show, which is why I present 3 methods.

-George

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

 

[JeffMachine]

Thanks Markros and GM, all are good suggestions. I used Markros statement since it worked perfectly for me.

Thanks ....