Re: Split Field or Extract a part

From: Rohtash Kapoor (rohtash_nospam_at_sqlmantra.com)
Date: 02/19/04 

Date: Wed, 18 Feb 2004 20:21:51 -0800

SET NOCOUNT ON

IF OBJECT_ID('TuTu') IS NOT NULL
   DROP TABLE TuTu

CREATE TABLE TuTu
(
   Col VARCHAR(100)
)

INSERT INTO TuTu VALUES ('3 L,DSP16-24,R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('1-4 Layer BD (1-10 Itms),20 R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('DblStk Pull-Off,R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('Reg Pull-Off,R-BadWood')
INSERT INTO TuTu VALUES ('Reg Pull-Off,R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('Reg Pull-Off,10 R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('Reg Pull-Off,R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('P16-24,R-BadWood')
INSERT INTO TuTu VALUES ('6 L,P25-27,6 R-BadWood')
INSERT INTO TuTu VALUES ('L,P16-24,20 R-BadWood')
INSERT INTO TuTu VALUES ('1-4 Layer BD (11+ Itms),2 R-Leaning/Shifted,4
R-TiHi')
INSERT INTO TuTu VALUES ('1-4 Layer BD (1-10 Itms),2
R-Leaning/Shifted,R-BadWood')
INSERT INTO TuTu VALUES ('1-4 Layer BD (1-10 Itms),R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('1-4 Layer BD (1-10 Itms),6 R-Leaning/Shifted')
INSERT INTO TuTu VALUES ('BANDS,3 L,P16-24,R-BadWood')
INSERT INTO TuTu VALUES ('BANDS,9 L,P16-24,3 R-BadWood')
INSERT INTO TuTu VALUES ('BANDS,2 L,P16-24,6 R-BadWood')
INSERT INTO TuTu VALUES ('1-4 Layer BD (1-10 Itms),4 R-BadWood')

SELECT REPLACE(
SUBSTRING(
LTRIM(REVERSE(SUBSTRING(col,1,CHARINDEX('R-', col)-1))),
1,
CHARINDEX(',', LTRIM(REVERSE(SUBSTRING(col,1,CHARINDEX('R-', col)-1)))))
, ',', '')
FROM TuTu 

---
Rohtash Kapoor
http://www.sqlmantra.com
"Robert" <anonymous@discussions.microsoft.com> wrote in message
news:11f5801c3f645$9adeb9d0$a301280a@phx.gbl...
> Sample Data:
> description
>
> -----------------------------------------------------------
> --------------------- 
> 3 L,DSP16-24,R-Leaning/Shifted
> 1-4 Layer BD (1-10 Itms),20 R-Leaning/Shifted
> DblStk Pull-Off,R-Leaning/Shifted
> Reg Pull-Off,R-BadWood
> Reg Pull-Off,R-Leaning/Shifted
> Reg Pull-Off,10 R-Leaning/Shifted
> Reg Pull-Off,R-Leaning/Shifted
> P16-24,R-BadWood
> 6 L,P25-27,6 R-BadWood
> L,P16-24,20 R-BadWood
> 1-4 Layer BD (11+ Itms),2 R-Leaning/Shifted,4 R-TiHi
> 1-4 Layer BD (1-10 Itms),2 R-Leaning/Shifted,R-BadWood
> 1-4 Layer BD (1-10 Itms),R-Leaning/Shifted
> 1-4 Layer BD (1-10 Itms),6 R-Leaning/Shifted
> BANDS,3 L,P16-24,R-BadWood
> BANDS,9 L,P16-24,3 R-BadWood
> BANDS,2 L,P16-24,6 R-BadWood
> 1-4 Layer BD (1-10 Itms),4 R-BadWood
>
>
> >-----Original Message-----
> >Could you give sample data.
> >
> >---
> >Rohtash Kapoor
> >http://www.sqlmantra.com
> >
> >"Robert" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:82f801c3f638$f650fc90$7d02280a@phx.gbl...
> >> I have been trying for some time to capture a part of a
> >> string to convert and sum a number accross my result
> set.
> >> The string looks like this: "abc,1 def,10 R-@@,xyz".  I
> >> have been looking for a way to split the field or
> extract
> >> a part of it specifically "10 R-@@" but the problem lies
> >> in the order not being consistent.  Sometimes the part
> is
> >> second, sometimes it is third and sometimes it ends up
> at
> >> the end.  The only thing that remains constant is
> the "R-
> >> @" where the @ is some variable sequence of letters. Any
> >> ideas to get me started?
> >>
> >> Thanks,
> >>
> >> Robert
> >
> >
> >.
> >