Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Indentify second occurance of a character 3

Status
Not open for further replies.
May 17, 2006
54
US
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 ...

 
So, is this enough?
Code:
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
 
There's a bunch of ways to do this. Shows here are 3 of them.

Code:
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
 
Thanks Markros and GM, all are good suggestions. I used Markros statement since it worked perfectly for me.

Thanks ....
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top