February 24, 2010 at 11:16 am
I have a column with data like below. I want the number between first period and the second period and without the periods . As you can see some have one period and some don't . Some have different number lengths.
I have tried charindex but i need this is one select statement
399.
400.
400.367.
400.35.369.
37.367.369.3
400.367.369.379.
February 24, 2010 at 11:41 am
Based on the data provided in your original post, what would be the expected result?
February 24, 2010 at 11:44 am
Lynn Pettis (2/24/2010)
Based on the data provided in your original post, what would be the expected result?
399. = NULL
400. = NULL
400.367. = 367
400.35.369. = 35
37.367.369.3 =367
400.367.369.379. = 367
Thank you
February 24, 2010 at 11:59 am
Here is some test code:
create table #TestTable (
DataValue varchar(24)
);
insert into #TestTable
select '399.' union all
select '400.' union all
select '400.367.' union all
select '400.35.369.' union all
select '37.367.369.3' union all
select '400.367.369.379.';
with SplitValues as (
select
tt.DataValue,
ds.ItemID,
ds.Item
from
#TestTable tt
cross apply dbo.DelimitedSplit(DataValue, '.') ds
)
select
tt.DataValue,
sv.Item
from
#TestTable tt
left outer join SplitValues sv
on (tt.DataValue = sv.DataValue
and sv.ItemID = 2);
Here is the code for the DelimitedSplit function:
CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit
February 24, 2010 at 12:04 pm
The following code is another way of doing it.
You might want to look at the first link in my signature for better ways to post to make it easier for folks to help you out.
declare @test-2 table (ColA varchar(50))
insert into @test-2
select '399.' UNION ALL
select '400.' UNION ALL
select '400.367.' UNION ALL
select '400.35.369.' UNION ALL
select '37.367.369.3' UNION ALL
select '400.367.369.379.'
select CASE WHEN Delim2Pos = 0 THEN NULL
ELSE SUBSTRING(ColA, Delim1Pos+1, Delim2Pos-Delim1Pos-1)
END
from (
select *,
Delim2Pos = CHARINDEX('.', ColA, Delim1Pos+1)
from (select * ,
Delim1Pos = CHARINDEX('.', ColA)
from @test-2) a
) b
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2010 at 6:25 am
Thanks ALL
Here is the solution I came up with.
DECLARE @x VARCHAR(80)
SET @x = '400.367.369.379'
SELECT
SUBSTRING(@x++'..',CHARINDEX('.',@x++'..')+1,CHARINDEX('.',SUBSTRING(@x++'..',CHARINDEX('.',@x++'..')+1,DATALENGTH(@x++'..')))-1)
Thanks for all your help
February 25, 2010 at 7:09 am
Thanks for responding back on how you resolved it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2010 at 7:10 am
FYI, if your data would ALWAYS have 3 periods in there, you could use the ParseName function to return the position that you wanted.
i.e.:
DECLARE @x VARCHAR(80)
SET @x = '400.367.369.379'
select PARSENAME(@x, 3)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 26, 2010 at 6:30 am
Or for another variation on a theme
IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL
DROP TABLE #FOO
select '399.' AS longcolumn INTO #FOO union all
select '400.' union all
select '400.367.' union all
select '400.35.369.' union all
select '37.367.369.3' union all
select '400.367.369.379.'
;
WITH cteTally
AS
(
SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pk
FROM master.sys.All_Columns
)
SELECT longcolumn, [2] AS Item FROM #FOO
CROSS APPLY
(
SELECT
[1],
[2],
[3],
[4]
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(longcolumn+'.', pk, CHARINDEX('.', longcolumn+'.', pk)-pk), '') AS Value
FROM cteTally
WHERE pk-1<LEN(longcolumn)+LEN('.') AND SUBSTRING('.' + longcolumn + '.', pk, 1)='.'
) AS Z
PIVOT
(
MAX(Value) for ROW in
(
[1],
[2],
[3],
[4]
)
)
AS pvt
)
AS Y
February 26, 2010 at 6:41 am
In fact there's a simpler way of writing this since you don't need the other columns.
IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL
DROP TABLE #FOO
select '399.' AS longcolumn INTO #FOO union all
select '400.' union all
select '400.367.' union all
select '400.35.369.' union all
select '37.367.369.3' union all
select '400.367.369.379.'
;
WITH cteTally
AS
(
SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pk
FROM master.sys.All_Columns
)
SELECT longcolumn, [2] AS Item FROM #FOO
CROSS APPLY
(
SELECT
[2]
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(longcolumn+'.', pk, CHARINDEX('.', longcolumn+'.', pk)-pk), '') AS Value
FROM cteTally
WHERE pk-1<LEN(longcolumn)+LEN('.') AND SUBSTRING('.' + longcolumn + '.', pk, 1)='.'
) AS Z
PIVOT
(
MAX(Value) for ROW in
(
[2]
)
)
AS pvt
)
AS Y
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply