DECLARE @x VARCHAR(32);
SET @x = 'AAAA BBBB KKKK GGGG';
SELECT
	STUFF
	( 
		STUFF
		(
			STUFF(@x,1,CHARINDEX(' ',@x),''),
			1,
			CHARINDEX(' ',STUFF(@x,1,CHARINDEX(' ',@x),'')),
			''
		),
		CHARINDEX
		(	' ',	
			STUFF
			(
				STUFF(@x,1,CHARINDEX(' ',@x),''),
				1,
				CHARINDEX(' ',STUFF(@x,1,CHARINDEX(' ',@x),'')),
				''
			)
		), LEN(@x),''
	);

quote:

---

Originally posted by sunitabeck

Hah! I knew there was something simpler. I had missed the constant BBBB in the specification.

In my own defense, mine is more general

---

select *
from   mytable
where value1 like '%STRING%'

DECLARE @sample TABLE ([value] VARCHAR(128))
INSERT @sample ([value])
VALUES ('AAAA BBBB KKKK GGGG')
	 , ('LLLL BBBB HHHH WWWWWSDR')
	 , ('HHHH BBBB DDDDD XXXXX')
	 
SELECT [value], PARSENAME(REPLACE([value], ' ', '.'), 2) FROM @sample

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    currentversion    REG_SZ    4.0   CCMPHDB2 
    currentversion    REG_SZ    K23   CCMPHDMDB 
    currentversion    REG_SZ    5.2   CCMPHDMVL 
    currentversion    REG_SZ    1E2   CCMPHDSDB1 

7.8   ABCDEFG
K123   ACBDNCDR
E345   ULTOIU

; with tbl as
(
	select	col	= '    currentversion    REG_SZ    4.0   CCMPHDB2 '	union all
	select	col	= '    currentversion    REG_SZ    K23   CCMPHDMDB '	union all
	select	col	= '    currentversion    REG_SZ    5.2   CCMPHDMVL '	union all
	select	col	= '    currentversion    REG_SZ    1E2   CCMPHDSDB1 '
)
select	col, word = substring(col, start, charindex(' ', col, start) - start)
from
(
	select	*, start = charindex('REG_SZ', col) + 6 + 4
	from	tbl
) t

col                                               word                                              
------------------------------------------------- ------------------------------------------------- 
    currentversion    REG_SZ    4.0   CCMPHDB2    4.0
    currentversion    REG_SZ    K23   CCMPHDMDB   K23
    currentversion    REG_SZ    5.2   CCMPHDMVL   5.2
    currentversion    REG_SZ    1E2   CCMPHDSDB1  1E2

(4 row(s) affected)

quote:

---

Originally posted by khtan

well, this works for me

; with tbl as
(
	select	col	= '    currentversion    REG_SZ    4.0   CCMPHDB2 '	union all
	select	col	= '    currentversion    REG_SZ    K23   CCMPHDMDB '	union all
	select	col	= '    currentversion    REG_SZ    5.2   CCMPHDMVL '	union all
	select	col	= '    currentversion    REG_SZ    1E2   CCMPHDSDB1 '
)
select	col, word = substring(col, start, charindex(' ', col, start) - start)
from
(
	select	*, start = charindex('REG_SZ', col) + 6 + 4
	from	tbl
) t

col                                               word                                              
------------------------------------------------- ------------------------------------------------- 
    currentversion    REG_SZ    4.0   CCMPHDB2    4.0
    currentversion    REG_SZ    K23   CCMPHDMDB   K23
    currentversion    REG_SZ    5.2   CCMPHDMVL   5.2
    currentversion    REG_SZ    1E2   CCMPHDSDB1  1E2

(4 row(s) affected)

Note : you only need the part in blue

---

KH
[spoiler]Time is always against us[/spoiler]

---

select substring(t.ab,1, charindex(' ',t.ab))
from (
select substring(ltrim(rtrim(value1)), patindex('%REG_SZ%',ltrim(rtrim(value1)))+10,100) as ab
from import ) t

BEGIN TRAN

USE tempdb
GO
IF EXISTS ( SELECT 1 FROM sys.objects WHERE [name] = 'split' AND [schema_id] = SCHEMA_ID('dbo'))
	DROP FUNCTION dbo.Split
GO

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO

DECLARE @sample TABLE (
	[SampleID] INT
	, [Text] VARCHAR(MAX)
	)
INSERT @sample VALUES
	  (1, 'currentversion    REG_SZ    4.0   CCMPHDB2 ')
    , (2, 'currentversion    REG_SZ    K23   CCMPHDMDB ')
    , (3, 'currentversion    REG_SZ    5.2   CCMPHDMVL ')
    , (4, 'currentversion    REG_SZ    1E2   CCMPHDSDB1 ')

-- trim the data
DECLARE @DataCleaned TABLE ([SampleID] INT, [Text] VARCHAR(MAX))
INSERT @DataCleaned ([SampleID], [Text]) SELECT [SampleID], [Text] FROM @Sample

DECLARE @rowCount INT = 1
WHILE @rowCount > 0
BEGIN
	UPDATE @DataCleaned SET [Text] = LTRIM(RTRIM(REPLACE([Text], '  ', ' '))) WHERE [Text] LIKE '%  %'
	SET @rowCount = @@ROWCOUNT
END

SELECT [SampleID], split.*
FROM
	@DataCleaned AS dc
	CROSS APPLY dbo.Split (' ', dc.[Text]) AS split
WHERE
	split.[pn] = 3

ROLLBACK

SampleID	pn	s
1	3	4.0
2	3	K23
3	3	5.2
4	3	1E2

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.