February 3, 2010 at 3:35 pm
Hi All,
I'm working with a third-party SQL database that uses a single varchar column to store multiple values separated by a semicolon. For instance, I have a column that stores the make, model, year and mileage of a vehicle which looks like this:
'Ford; Taurus; 2001; 99,000'
How can I write a query to just return all of the models from this column?
Thanks!
Ruprecht
February 3, 2010 at 3:39 pm
Will the models always be the second value in the delimited list? Or will you have to join this to a model table that holds all the models?
February 3, 2010 at 3:43 pm
Yes, they will always be in the second place in the list. We shouldn't have to join the results to another table.
February 3, 2010 at 3:46 pm
if the model will always be the second value in the delimited list then i would go online and bring down the split function. Which is below
---------------------------------------------------------------------
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
--------------------------------------------------------------------------
--once the above is created then u can run the below.
Declare @string varchar(200)
Set @string = 'Ford; Taurus; 2001; 99,000'
--run this to see what gets returned
Select *
From dbo.Split( @string,';')
--run this to isolate the model
Select *
From dbo.Split( @string,';')
where id = 2
--I'm pretty sure there are going to be several people who think u should do it a different way w/ substrings and what not but if u dont have the split function already then u should because it will come in handy.
February 3, 2010 at 4:03 pm
actually i dont think that will work in this instance
this will WORK however IT COULD BE DONE A BIT CLEANER HOWEVER I'M LEAVING FOR THE DAY
Declare @string varchar(200)
Set @string = 'Ford; homeslice WORKABLE ; 2001; 99,000'
select left( rtrim(ltrim( substring(@string
,( charindex( ';',ltrim(rtrim(@string)), 0) + 1 )
, 200)))
, charindex( ';',ltrim(rtrim(rtrim(ltrim( substring(@string
,( charindex( ';',ltrim(rtrim(@string)), 0) + 1 )
, 200))))), 0)-1)
February 3, 2010 at 4:13 pm
THIS WILL WORK AS WELL. A LITTLE CLEANER
Declare @string varchar(200)
Set @string = 'Ford; homeslice SSSSSSHAABABSHBA WORKABLE ; 2001; 99,000'
SELECT RTRIM(LTRIM( SUBSTRING(@STRING, LEN(LEFT ( @STRING, CHARINDEX(';', @STRING)))+1 , CHARINDEX(';', @STRING, 6) - LEN(LEFT ( @STRING, CHARINDEX(';', @STRING)))-1) ))
--'Ford;
February 3, 2010 at 5:48 pm
BaldingLoopMan (2/3/2010)
if the model will always be the second value in the delimited list then i would go online and bring down the split function. Which is below---------------------------------------------------------------------
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
--------------------------------------------------------------------------
--once the above is created then u can run the below.
Declare @string varchar(200)
Set @string = 'Ford; Taurus; 2001; 99,000'
--run this to see what gets returned
Select *
From dbo.Split( @string,';')
--run this to isolate the model
Select *
From dbo.Split( @string,';')
where id = 2
--I'm pretty sure there are going to be several people who think u should do it a different way w/ substrings and what not but if u dont have the split function already then u should because it will come in handy.
Take a look at the following for a different slant on looping...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2010 at 8:37 pm
Ruprecht (2/3/2010)
Hi All,I'm working with a third-party SQL database that uses a single varchar column to store multiple values separated by a semicolon. For instance, I have a column that stores the make, model, year and mileage of a vehicle which looks like this:
'Ford; Taurus; 2001; 99,000'
How can I write a query to just return all of the models from this column?
Thanks!
Ruprecht
This will get all of the columns, then return just the models.
-- notice how a table is defined (DDL) and values are inserted into it (DML)?
-- you should do this to help us help you... after all, we're all volunteers,
-- and anything that we can just cut-and-paste into SSMS will be worked on faster!
declare @test-2 Table (VehicleInfo varchar(500))
insert into @test-2
select 'Ford; Taurus; 2001; 99,000' UNION ALL
select 'Toyota; Corolla; 2010; 30,000' UNION ALL
select 'Chevrolet; Silverado; 2000; 165,000' UNION ALL
select 'Dodge; Caravan; 1995; 285,000'
;with CTE1 AS
(
select VehicleInfo,
Column1EndPos = CharIndex(';', VehicleInfo)
from @test-2
), CTE2 AS
(
select VehicleInfo,
Column1EndPos,
Column2EndPos = CharIndex(';', VehicleInfo, Column1EndPos + 1)
from CTE1
), CTE3 AS
(
select VehicleInfo,
Column1EndPos,
Column2EndPos,
Column3EndPos = CharIndex(';', VehicleInfo, Column2EndPos + 1)
from CTE2
), CTE4 AS
(
select [Make] = ltrim(rtrim(left(VehicleInfo, Column1EndPos-1))),
[Model] = ltrim(rtrim(substring(VehicleInfo, Column1EndPos+1, Column2EndPos - Column1EndPos - 1))),
[Year] = ltrim(rtrim(substring(VehicleInfo, Column2EndPos+1, Column3EndPos - Column2EndPos -1 ))),
[Mileage] = ltrim(rtrim(substring(VehicleInfo, Column3EndPos+1, len(VehicleInfo)-Column3EndPos)))
from CTE3
)
select Model from CTE4
If all you will ever need is the model, you can replace everything after ", CTE3 AS" with:
select [Model] = ltrim(rtrim(substring(VehicleInfo, Column1EndPos+1, Column2EndPos-Column1EndPos-1)))
from CTE2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 3, 2010 at 9:15 pm
If the data is actually as you say it is, then using Wayne's good test data, you can cheat like hell 😉
-- notice how a table is defined (DDL) and values are inserted into it (DML)?
-- you should do this to help us help you... after all, we're all volunteers,
-- and anything that we can just cut-and-paste into SSMS will be worked on faster!
declare @test-2 Table (VehicleInfo varchar(500))
insert into @test-2
select 'Ford; Taurus; 2001; 99,000' UNION ALL
select 'Toyota; Corolla; 2010; 30,000' UNION ALL
select 'Chevrolet; Silverado; 2000; 165,000' UNION ALL
select 'Dodge; Caravan; 1995; 285,000'
SELECT LTRIM(PARSENAME(REPLACE(VehicleInfo,';','.'),3))
FROM @test-2
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2010 at 9:47 am
@jeff - thinking outside the box again...:smooooth:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 4, 2010 at 1:35 pm
very nice. i wouldn't have thought about using the parse name. nice work.
February 5, 2010 at 4:58 pm
WayneS (2/4/2010)
@Jeff - thinking outside the box again...:smooooth:
BaldingLoopMan (2/4/2010)
very nice. i wouldn't have thought about using the parse name. nice work.
Heh... thanks guys. But I prefer to think of it as "Thinking INSIDE the box"... the box known as T-SQL. 🙂
I have a drawing in my office and the words above it are "Before you can think outside the box, you must first realize.... you're in a box." 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply