January 25, 2011 at 5:36 am
Hi
In my table values below like that
Table1
State
'Indiana','Kentucky','Ohio'
City
'Addyston','Alexandria','Alpha','Amelia','Anderson','Arlington Heights','Augusta','Aurora','Avondale','Batavia',
,'Bennington','Bethel','Blanchester','Blanchet','Blue Ash',
'Boston','Bromley','Brooksville','Brookville','Brown County','Brownsville','Buford','Burlington','Butler','California'
I have to make it like table like
city - expected result
City
'Addyston','Alexandria','Alpha','Amelia','Anderson'
'Arlington Heights','Augusta','Aurora','ts','B'
'Batesville','Bath','Beavercreek','Bellbrook','Bellevue'
'Blue Ash','Boston','Bromley','Brooksville','Brookvil'
because sometimes city will come more length.so i have to split it for full index search
So how to split the csv field like above?
Can anyone help me for this scenario?
Thanks
January 26, 2011 at 12:02 pm
In this example full text index is very wrong solution.
You should store each value in separate table row and use a normal index.
Split function is here:
-- Create sysTally table
SELECT TOP 262144 N = IDENTITY(int,0,1)
INTO dbo.sysTally
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
GO
-- Make it space-efficient and ordered
ALTER TABLE dbo.sysTally ADD CONSTRAINT PK_sysTally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
create function dbo.str_Split
--Splits a string. Supports very long strings and is very fast.
--EXAMPLES:
--SELECT s.id FROM dbo.str_Split( null, ',' ) s
--SELECT s.id FROM dbo.str_Split( '', ',' ) s
--SELECT s.id from dbo.str_Split('a; b ;', ';') s
(
@STR varchar(max),
@delimiter CHAR(1) = ','
)
returns @ret TABLE
(id varchar(8000)
)
AS
BEGIN
DECLARE @len INT SET @len = datalength(@str)
IF @len > 262143 ---select MAX(n) FROM dbo.sysTally
BEGIN
declare @err int
set @err = 'dbo.str_Split: Input string is longer than there are rows in sysTally table! Please, add more rows to sysTally.'+0
END
-- There is no loop. Just one select command.
INSERT INTO @ret(id)
SELECT id = SUBSTRING
(@STR,
t.N+1,
ISNULL
(NULLIF(CHARINDEX(@delimiter,@str,t.N+1), 0),
@len+1
)-t.N-1
)
FROM dbo.sysTally t
WHERE t.N <= @len
AND ( t.N=0 or SUBSTRING(@str,t.N,1) = @delimiter )
RETURN
END
January 27, 2011 at 6:42 am
hi
Thanks for your function.
Your function will spilt single City.But i have to split some array of City
For Ex :
Table1 by 4
Addyston,Alexandria,Alpha,Amelia
Anderson,Arlington Heights,Augusta
Aurora,Avondale,Batavia,Batesville
Table1 by 3
Addyston,Alexandria,Alpha
Amelia,Anderson,Arlington Heights
Augusta,Aurora,Avondale
Batavia,Batesville
4,3 is my N
I have to tried alter ur function t.N+1.
However i didnt get. can u guide me for this?
January 27, 2011 at 7:29 am
If you really have to...
split to one city, than rejoin them with less cities per row.
But I would not join them at all. I would leave one city per row.
Google "database normalisation", you will learn a lot.
January 28, 2011 at 12:03 am
Thanks.I created a function for that.
Below is the function.
May be useful for someone in future.
ALTER FUNCTION dbo.Split
(
@RowData
nvarchar(2000),
@SplitOn
nvarchar(5),
@SplitNo
int
)
RETURNS
@RtnValue table
(
--Id int identity(1,1),
Data
nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Declare @SpCnt int
Declare @DataToRow nvarchar(2000)
Set @Cnt = 1
Set @SpCnt = 1
Set @DataToRow = ''
While (Charindex(@SplitOn,@RowData)>0)
Begin
If @DataToRow=''
Set @DataToRow = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Else
Set @DataToRow = @DataToRow + @SplitOn + ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
If (@SplitNo=@SpCnt)
Begin
Set @SpCnt = 0
Insert Into @RtnValue (data) Select @DataToRow
Set @DataToRow = ''
End
Set @Cnt = @Cnt + 1
Set @SpCnt = @SpCnt + 1
End
If @DataToRow<>''
Select @DataToRow=@DataToRow+ @SplitOn +@RowData
Else
Select @DataToRow=@RowData
Insert Into @RtnValue (data)
Select @DataToRow
Return
END
February 13, 2011 at 12:57 pm
That's likely going to be quite slow because of the While Loop.
Your function will spilt single City.But i have to split some array of City
Why? :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply