col1     col2
-----    -----
SMITH    1234

quote:

---

Originally posted by vaibhavktiwari83

Look this

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141925[/url]

Vaibhav T

---

CREATE FUNCTION dbo.fnStrip(@string AS VARCHAR(100)) RETURNS @SPLIT TABLE
(
	String   VARCHAR(100),
	Number VARCHAR(100)
)
AS
BEGIN
	DECLARE @Len INT
	DECLARE @Pos INT
	DECLARE @OutString   VARCHAR(100)
	DECLARE @OutNumber VARCHAR(100)
	DECLARE @ch CHAR(1)
	
	SELECT @Len = LEN(@string), @Pos = 1, @OutString = '', @OutNumber = ''

	WHILE @Pos <= @Len
	BEGIN
		SET @ch = SUBSTRING(@string, @pos, 1)
		IF ISNUMERIC(@ch) != 0
			SET @OutNumber = @OutNumber + @ch
		ELSE
			SET @OutString = @OutString + @ch

		SET @Pos = @Pos + 1		
	END

	INSERT INTO @SPLIT VALUES (@OutString, @OutNumber)
	RETURN 	
END
GO

CREATE TABLE #tmp (TestString VARCHAR(100))
GO

INSERT INTO #tmp
SELECT 'AB12CD3EFGH456'
UNION ALL SELECT 'abce123fghi456'
GO

SELECT *
FROM #tmp t
CROSS APPLY dbo.fnStrip(t.TestString)
GO

DROP TABLE #tmp
GO

DROP FUNCTION dbo.fnStrip
GO