multiple parse????

[futbwal15]

say i have a field that lists car year, car manufacturer, and car model.....is it possible to parse this into 3 different outputs? considering they will be different lengths?

 

[SgtJarrow]

earthandfire and futbwal15 gave you two great examples in thread183-1332930

This is ultimately going to depend if there is any identifying way to separate the parts.

For example, if you data is like 1997 Ford Mustang, the spaces can be used. But 2002 Cheverolet Monte Carlo would fail, because there is an extra space. But then it may work, becuase the year and manufacturer make up the first two parts and you could take anything after the manufacturer space separator as the model.

Provide some sample data and someone may be able to help a bit if you can't get the SUBSTRING function working.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[bborissov]

The most positive answer i can get is "Maybe". And this is not because I didn't want to help, but I don't know your data. Can you post simple data and desired result from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[futbwal15]

data will look like this:
2000 Ford Mustang
1999 Harley Davidson SE1224
2005 chevrolet cobalt
2001 Mazda 6


and the end result i want is for 3 columns to be created titled Year (where the year will be displayed), Manufacturer (where "ford" for example will be, and then Make (where "mustang" will appear) this is the code i have right now:

select
f.matteridstring,
f.custom1,
Substring(l.itemtext, 12, 2) As itemtext
from contacts c, parties p, fileinfo f, listbox l
where
l.itemtext like 'Lemon Law%' and
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id

 

[bborissov]

futbwal15,
Try:

--- Preparing Test data
declare @Temp table (descr varchar(200))
insert into @Temp VALUES('2000 Ford Mustang')
insert into @Temp VALUES('1999 Harley Davidson SE1224')
insert into @Temp VALUES('2005 chevrolet cobalt')
insert into @Temp VALUES('2001 Mazda 6')
--- End

SELECT LEFT(descr,4) AS cYesr,
       REVERSE(SUBSTRING(REVERSE(SUBSTRING(descr,5,8000)),CHARINDEX(' ',REVERSE(SUBSTRING(descr,5,8000))),8000)) AS Manufacturer,

       REVERSE(LEFT(REVERSE(descr),CHARINDEX(' ',REVERSE(descr))-1)) AS Model
FROM @temp

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

and here is my solution

create table blah2(col varchar(666))

insert blah2 values('2000 Ford Mustang')
insert blah2 values('1999 Harley Davidson SE1224')
insert blah2 values('2005 chevrolet cobalt')
insert blah2 values('2001 Mazda 6')



select LEFT(col,CHARINDEX(' ',col)-1)AS year,
SUBSTRING(col,
LEN(LEFT(col,CHARINDEX(' ',col)+1)),
len(col) -((len(RIGHT(RTRIM(col),CHARINDEX(' ',REVERSE(RTRIM(col)))-1))) +len(LEFT(col,CHARINDEX(' ',col)-1)))-1) AS Manufacturer,
RIGHT(RTRIM(col),CHARINDEX(' ',REVERSE(RTRIM(col)))-1) AS Make
from blah2

and of course the site is down ;-(

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

So... This is pretty ugly because you are storing 2 different field in to 1 field. You really should normalize your database so that Year, Make, And Model are 3 seperate columns.

Anyway, this is probably what you are looking for. As others have mentioned in other threads, this query is problematic because it will provide false results when the data is bad.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('2000 Ford Mustang')
Insert Into @Temp Values('1999 Harley Davidson SE1224')
Insert Into @Temp Values('2005 chevrolet cobalt')
Insert Into @Temp Values('2001 Mazda 6')
Insert Into @Temp Values('2001 MyCar')
Insert Into @Temp Values('2001')

Select Left(Data, CharIndex(' ', Data)),
       Replace(Replace(Data, Left(Data, CharIndex(' ', Data)), ''), LTrim(Reverse(Left(Reverse(Data), CharIndex(' ', Reverse(Data))))), ''),
       LTrim(Reverse(Left(Reverse(Data), CharIndex(' ', Reverse(Data)))))
From   @Temp

Notice what happens when the combined field has bad data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

how would i implement this into the code i already have?

i dont need the insert values commands

 

[bborissov]

What code?
futbwal15 you put us in some Matrix thing
My crystal ball not working today, so what is the code. If it is the code you posted here:

select f.matteridstring,
       f.custom1,
       Substring(l.itemtext, 12, 2) As itemtext
from contacts c,
     parties p,
     fileinfo f,
     listbox l
where l.itemtext like 'Lemon Law%' and
      c.contactid = p.contactid    and
      f.fileno = p.fileno          and
      f.filetype = l.id

How should we know from what table you must get the results? And what is Lemon Law

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>Lemon Law

a lemon is a car that has been sold to you by a dealer but is not in good condition (the dealer repaired it but never told you the car was flooded, damaged or whatever)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

>> And what is Lemon Law

You are not allowed to purchase lemons out of season!


HAHA. Seriously though. It's an american law the protects people from purchasing cars that have many problems with them.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

this is the code i already have:

select
f.matteridstring,
f.custom1,
Substring(l.itemtext, 12, 2) As itemtext
from contacts c, parties p, fileinfo f, listbox l
where
l.itemtext like 'Lemon Law%' and
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id



now when i run my query i want to be able to parse f.custom1 by using the spaces in the data. because some of the data reads "2003 Ford Mustang".....
so i need 3 separate columns....one that has 2003, one that has Ford, and one that has Mustang.

 

[gmmastros]

Somehow I think you are more interested in having someone write this for you than you are in learning the process of writing your own queries. Sad, really.

select 
f.matteridstring,f.custom1,
Substring(l.itemtext, 12, 2) As itemtext,
Left(f.custom1, CharIndex(' ', f.custom1)),       Replace(Replace(f.custom1, Left(f.custom1, CharIndex(' ', f.custom1)), ''), LTrim(Reverse(Left(Reverse(f.custom1), CharIndex(' ', Reverse(f.custom1))))), ''),LTrim(Reverse(Left(Reverse(f.custom1), CharIndex(' ', Reverse(f.custom1)))))
from contacts c, parties p, fileinfo f, listbox l
where
l.itemtext like 'Lemon Law%' and
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

not the case, just confused because i keep getting errors.

 

[gmmastros]

Does the query work? Are you getting errors with it?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

yes

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of left function.

 

[bborissov]

Lemon Law, HA!

I like it, especially the name.
futbwal15, what errors you get?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

lol

you can not use SUBSTRING on text datatypes
why do you store 30 characters in a text data type anyway?

anyway do a replace

replace f.custom1
with
convert(varchar(500),f.custom1) and same for l.itemtext



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[futbwal15]

replace f.custom1 with convert(varchar(500),f.custom1)
replace l.itemtext with convert(varchar(500),l.itemtext)
select
f.matteridstring,f.custom1,
Substring(l.itemtext, 12, 2) As "Year",
Left(f.custom1, CharIndex(' ', f.custom1)), Replace(Replace(f.custom1, Left(f.custom1, CharIndex(' ', f.custom1)), ''), LTrim(Reverse(Left(Reverse(f.custom1), CharIndex(' ', Reverse(f.custom1))))), ''),LTrim(Reverse(Left(Reverse(f.custom1), CharIndex(' ', Reverse(f.custom1)))))
from contacts c, parties p, fileinfo f, listbox l
where
l.itemtext like 'Lemon Law%' and
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id




gives me this error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

 

[bborissov]

There is no such command as REPLACE in T-SQL:

select f.matteridstring,
       f.custom1,
       Substring(Convert(varchar(500),l.itemtext), 12, 2) As "Year",
        Left(Convert(varchar(500),f.custom1),
                              CharIndex(' ', f.custom1)),     
           Replace(Replace(Convert(varchar(500),f.custom1),
           Left(Convert(varchar(500),f.custom1),
                        CharIndex(' ', Convert(varchar(500),f.custom1))), ''),
LTrim(Reverse(Left(Reverse(Convert(varchar(500),f.custom1)),
CharIndex(' ', Reverse(Convert(varchar(500),f.custom1)))))),
''),
LTrim(Reverse(Left(Reverse(Convert(varchar(500),f.custom1)), CharIndex(' ', Reverse(Convert(varchar(500),f.custom1))))))
from contacts c, parties p, fileinfo f, listbox l
where
l.itemtext like 'Lemon Law%' and
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id

I hope I am not missing some bracket on the way.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP