Need help in split

Question

Need help in split

GuruGPrasad

SSCommitted

Points: 1768
More actions
March 23, 2012 at 4:12 am

#391854

Hi,
'Alter table dbo.TableA add test int'
From this i need to get the Schema name and table name using split..
Here i have one solution. Is there is any other easy way to split this ?
declare @STR as nvarchar(max),@SourceSchema nvarchar(50),@SourceName nvarchar(50)
set @STR='Alter table dbo.TableA add test int'
select @STR=element from dbo.SplitArray(@str,'table ')
select top 1 @STR=element from dbo.SplitArray(@str,' ')
select element from dbo.splitarray(@str,'.')
CREATE FUNCTION dbo.SplitArray(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Element VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
Regards
Guru

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 1

declare @STR as nvarchar(max),@SourceSchema nvarchar(50),@SourceName nvarchar(50)

select @SourceSchema=RTRIM(LTRIM(SUBSTRING('Alter table dbo.TableA add test int',1,CHARINDEX('.','Alter table dbo.TableA add test int',0)-1)))

Select @SourceSchema=element from splitarray(@SourceSchema,'table ')

Print @SourceSchema

select @SourceName= RTRIM(LTRIM(SUBSTRING('Alter table dbo.TableA add test int',CHARINDEX('.','Alter table dbo.TableA add test int',0)+LEN('.'),LEN('Alter table dbo.TableA add test int'))))

select @SourceName=Left(@SourceName,CHARINDEX(' ',@SourceName,0))

Print @SourceName

another solution

REgards

Guru

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

take a look in my signature block for Jeff's string splitter

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 3

GuruGPrasad

SSCommitted

Points: 1768

March 23, 2012 at 4:57 am

#1463272

Thank you

anthony.green

Regards

Guru

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Jeff's string splitter is the quickest non-CLR method.

Here's a way using XML: -

DECLARE @STR AS NVARCHAR(max), @XMLList AS XML;

SET @STR = 'Alter table dbo.TableA add test int';

SELECT @XMLList = CAST(REPLACE(REPLACE(REPLACE(

CAST((SELECT @STR FOR XML PATH('list')) AS NVARCHAR(MAX)),

' ','" /><y i="'),'<list>','<list><y i="'),'</list>','" /></list>') AS XML);

SELECT SUBSTRING(split,1,CHARINDEX('.',split)-1), SUBSTRING(split,CHARINDEX('.',split)+1,LEN(split))

FROM (SELECT x.y.value('.','VARCHAR(MAX)')

FROM @XMLList.nodes('list/y/@i') AS x(y)) a(split)

WHERE CHARINDEX('.',split) > 0;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 5

GuruGPrasad

SSCommitted

Points: 1768

March 23, 2012 at 5:18 am

#1463281

Hi Cadavre

Thanks

Regards

Guru