/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2(
  @TargetStr varchar(8000),
  @SearchedStr varchar(8000),
  @Occurrence int)
RETURNS int
AS
BEGIN
  DECLARE @pos int, @counter int, @ret int
  SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
  SET @counter = 1
  IF @Occurrence = 1 
    SET @ret = @pos
  ELSE
    BEGIN
      WHILE (@counter < @Occurrence)
        BEGIN
          SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
          SET @counter = @counter + 1
          SET @pos = @ret
        END
    END
  RETURN(@ret)
END
GO
/*
---------------------------------------- ---------------------------------------- ----------------------------------------
24W                                       529                                      22
3102                                      DENIM                                    8
66552266                                  green                                    blue
*/
SELECT 
  SUBSTRING(TheExample,1,dbo.CHARINDEX2(' ', TheExample, 1)),
  SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 1),dbo.CHARINDEX2(' ', TheExample, 2) - dbo.CHARINDEX2(' ', TheExample, 1)),
  SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 2),dbo.CHARINDEX2(' ', TheExample, 3) - dbo.CHARINDEX2(' ', TheExample, 2))
from (
SELECT '24W 529 22 132 110 123 -13 516 ' AS TheExample UNION ALL
SELECT '3102 DENIM 8 99 0 213 213 0 213 312 ' UNION ALL
SELECT '66552266 green blue 77p 1111 2222 33333 '
) X

SELECT 
  style = SUBSTRING(f1,1,dbo.CHARINDEX2(' ', f1, 1)),
  color = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 1),dbo.CHARINDEX2(' ', f1, 2) - dbo.CHARINDEX2(' ', f1, 1)),
  size_1 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 2),dbo.CHARINDEX2(' ', f1, 3) - dbo.CHARINDEX2(' ', f1, 2)),
  size_2 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 3),dbo.CHARINDEX2(' ', f1, 4) - dbo.CHARINDEX2(' ', f1, 3))
from dbo.NEWDUNDD_SEACX