SQLTeam.com | Weblogs | Forums

Update 3 Columns by Splitting a 4th Column

I have a split function that splits a single column which contains "dbo.Table1.Column1" delimited by a period

I need to update 3 columns named Schema, TableName and ColumnName in each row. I'm struggling with the TSQL code.
This works well to perform the split but I don't know how to update the table columns with the split values:
DECLARE @DelimitedString NVARCHAR(128)
SET @DelimitedString = 'dbo.MyTable.MyColumn'
select Data FROM dbo.fnSplitString(@DelimitedString, '.') where Id = 1
select Data FROM dbo.fnSplitString(@DelimitedString, '.') where Id = 2
select Data FROM dbo.fnSplitString(@DelimitedString, '.') where Id = 3

Thanks for any tips.

DECLARE @DelimitedString NVARCHAR(128)
SET @DelimitedString = 'dbo.MyTable.MyColumn'
SELECT PARSENAME(@DelimitedString, 1)
SELECT PARSENAME(@DelimitedString, 2)
SELECT PARSENAME(@DelimitedString, 3)
1 Like

I like the simplicity of your solution, ScottPletcher. Thanks very much!

update [dbo].[Table1]
set [Schema] = (SELECT PARSENAME(ColumnName, 3))

update [dbo].[Table1]
set TableName = (SELECT PARSENAME(ColumnName, 2))

update [dbo].[Table1]
set ColName = (SELECT PARSENAME(ColumnName, 1))

update [dbo].[Table1]
set [Schema] = PARSENAME(ColumnName, 3),
    TableName = PARSENAME(ColumnName, 2),
    ColName = PARSENAME(ColumnName, 1)