Help me with SQL query

[anderslundsgard]

I need to populate a new colum with data from one other column in the same table: Table Has columns id, name, part: Id name part "122" "E3, SS, ABC" "NULL" "123" "E3, BB, JKL" "NULL" … … … I want to have a script that fills the part column with the string to the right of ‘,’ from the name column. The result should look like this: Id name part "122" "E3, SS, ABC" "ABC" 123" "E3, BB, JKL" "JKL" … … …

_____________________________ ...and justice for all

[Blue_Boy]

SELECT m.id,m.name,
SUBSTRING(m.[Name],LEN(m.[Name])-CHARINDEX(',',m.[Name])+1,LEN(m.[Name])) AS Part

FROM mytable m

Hope this will help you.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[Niladri_Biswas]

Hi man, You solution/query is good for this context but it fails in some other cases: e.g. a) INPUT:

'aaa,bbb,ccc'

SELECT SUBSTRING('aaa,bbb,ccc',LEN('aaa,bbb,ccc')-CHARINDEX(',','aaa,bbb,ccc')+1,LEN('aaa,bbb,ccc')) OUTPUT:

**,**ccc

b) INPUT:

'A,B,C'

SELECT SUBSTRING('A,B,C',LEN('A,B,C')-CHARINDEX(',','A,B,C')+1,LEN('A,B,C')) OUTPUT:

**,**C

c) INPUT:

AQQQQQ,BFFFFF,CYYYYY

SELECT SUBSTRING('AQQQQQ,BFFFFF,CYYYYY',LEN('AQQQQQ,BFFFFF,CYYYYY')-CHARINDEX(',','AQQQQQ,BFFFFF,CYYYYY')+1,LEN('AQQQQQ,BFFFFF,CYYYYY')) OUTPUT:

**,**CYYYYY

Niladri Biswas

[Niladri_Biswas]

Hi, I have written a Stored Procedure for doing this. The Table Name is TBLEXTRACT_STRING with the same columns and the same values

**ALTER PROCEDURE SP_UPDATELASTCOLUMN
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- VARIABLE DECLARATION SECTION

	DECLARE @CNTRECORDS INT
	DECLARE @NAMEVALS VARCHAR(50)
	DECLARE @I INT

-- SETTING THE INITIAL VALUES
	SET @I  = 1
 
    SELECT @CNTRECORDS = COUNT(\*) FROM TBLEXTRACT\_STRING

	SELECT ROW\_NUMBER() OVER (ORDER BY ID) AS ROWID,\* INTO #TEMP FROM TBLEXTRACT\_STRING

	
	WHILE ( @I <= @CNTRECORDS )

		BEGIN
				SELECT @NAMEVALS = \[NAME\] 
				FROM #TEMP
				WHERE ROWID = @I
				
				UPDATE TBLEXTRACT\_STRING

				SET PART = (SELECT TOP 1 STRINGVAL FROM DBO.FNSPLIT(@NAMEVALS,',') 
							ORDER BY COUNTER DESC)

				WHERE ID  = (121 + @I)					
				SET @I = @I + 1		
		END

	DROP TABLE #TEMP

END
GO**

And the Split function(fnSplit) is as under

**ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin

	Declare @newstring as varchar(100)
	Declare @pos as int
	Declare @i as int
	Declare @c as int	

	set @newstring = '';		
	set @i = 1
	set @c = 0

	set @pos = CHARINDEX(@delimeter, @oldstring) 

	WHILE (@i != 0)

		Begin

			set @c = @c +1
			insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
			
			set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))

			set @pos = CHARINDEX(@delimeter, @oldstring)

			set @i = @pos;
			if (@i = 0)
            Begin
                set @i = 0;
					set @c = @c +1
            
				insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
			
			
            End
		End

		return

End**

Hope this helps :)

Niladri Biswas