How to create a calculated column with the 1 result of a split function
-
Hello everybody,
basically i have a column which contains data like
(string1, string2......etc)
(other string1, other string2......etc)
i need to create a calculated column which contains the string1.
so, the result must be:
string1
other string1
how can i do that?
thank you.:-)
-
This should do the trick:
CREATE FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))
RETURNS varchar(500)
AS
BEGIN
RETURN (
SELECT TOP 1 SUBSTRING(@Parameter,N+2,CHARINDEX(',',@Parameter,N+1)-N-2)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
ORDER BY N
)
END
CREATE TABLE Test (
valueList varchar(8000),
firstValue as dbo.FirstToken(valueList)
)
INSERT INTO Test VALUES ('(string1, string2, string3, string4)')
INSERT INTO Test VALUES ('(other string1, other string2, other string3, other string4)')
SELECT * FROM Test
You'll need a Tally table to make this code work. If you don't have one, read this article[/url].
Hope this helps
Gianluca
-- Gianluca Sartori
-
Hi Gianluca,
This is the first time i hear about a tally table.
I have simply created a tally table and also executed your script.
It seems not working..
I'm getting as result the second string instead of the first one
Thank you(grazie :))
-
evald (4/14/2010)
Hi Gianluca,This is the first time i hear about a tally table.
Take a look at that article, it's an eye-opener. I'm sure you'll find it enlightening.
I have simply created a tally table and also executed your script.
It seems not working..
I'm getting as result the second string instead of the first one
Thank you(grazie :))
Very strange, I tested the code before submitting.
Can you post the whole script for table, sample data and function please?
-- Gianluca Sartori
-
What about something like this?
Use mydbTestGO
CREATE FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))
RETURNS varchar(500)
AS
BEGIN
RETURN
(
rtrim(ltrim(substring(LEFT(@Parameter,patindex('%,%', @Parameter)),2,len(LEFT(@Parameter,patindex('%,%', @Parameter))) - 2)))
)
END
GO
CREATE TABLE Test (
valueList varchar(8000),
firstValue as dbo.FirstToken(valueList)
)
INSERT INTO Test VALUES ('(aa, string2_aa, string3, string4)')
INSERT INTO Test VALUES ('(ab, string1_ab, other string2, other string3, other string4)')
INSERT INTO Test VALUES ('(ac, string2_ac, string3, string4)')
INSERT INTO Test VALUES ('(ad, string1_ad, other string2, other string3, other string4)')
SELECT * FROM Test
Result
aa
ab
ac
ad
-
here is the code i have used
create the tally table on the TallyTest (taken from the article)
--=============================================================================
-- Setup
--=============================================================================
USE TallyTest
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
--===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1
--===== Populate the table using the loop and couner
WHILE @Counter <= 8000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END
create test table and function
USE TallyTest
GO
CREATE FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))
RETURNS varchar(500)
AS
BEGIN
RETURN (
SELECT TOP 1 SUBSTRING(@Parameter,N+2,CHARINDEX(',',@Parameter,N+1)-N-2)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
ORDER BY N
)
END
GO
CREATE TABLE Test (
valueList varchar(8000),
firstValue as dbo.FirstToken(valueList)
)
INSERT INTO Test VALUES ('(aa, string2_aa, string3, string4)')
INSERT INTO Test VALUES ('(ab, string1_ab, other string2, other string3, other string4)')
INSERT INTO Test VALUES ('(ac, string2_ac, string3, string4)')
INSERT INTO Test VALUES ('(ad, string1_ad, other string2, other string3, other string4)')
SELECT * FROM Test
-
I forgot to add one line of code to the function... sorry.
ALTER FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))
RETURNS varchar(500)
AS
BEGIN
SET @Parameter = ',' + @Parameter + ','
RETURN (
SELECT TOP 1 SUBSTRING(@Parameter,N+2,CHARINDEX(',',@Parameter,N+1)-N-2)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
ORDER BY N
)
END
Probably your solution scales better than mine, take the time to test it with a large load. Thanks for sharing.
-- Gianluca Sartori
-
I'm testing on a copy of a production table.
Basically i have a ssis package which populate this table.
There are about 100.000 rows 🙂
Its very fast.
🙂
thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply