Manipulating string to return first value before space

[tc]

I have data coming back like below

140 KB 8 KB 1450 KB

I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.

[Satish Seshadri]

declare @Source varchar(20)
declare @Destination varchar(20)

select @Source = '140 KB 8 KB 1450 KB '
select @Destination = REPLACE (@Source, ' KB', '&#39
select @Destination
-- result will be 140 8 1450 (in varchar)

Hope this helps.
Satish





------------
tc at 12/10/2001 10:35:20 AM

I have data coming back like below

140 KB 8 KB 1450 KB

I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.

[Juergen Leis]

-- If you want the first value as strin (without error handling e.g. NULL)

SUBSTRING( @MyString, 1, CHARINDEX( @MyString, ' ' ) - 1 )

-- If you want the first value as int (without error handling)
convert( int, SUBSTRING( @MyString, 1, CHARINDEX( @MyString, ' ' ) - 1 ) )

------------
tc at 12/10/2001 10:35:20 AM

I have data coming back like below

140 KB 8 KB 1450 KB

I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.

[dba723]

fyi,

you can also do this to return all values in the string:

declare @str varchar(100),@val int,@atend int
select @str='50kb 120kb 30kb'
select @atend=0
while @atend=0
begin
select @val=substring(@str,1,charindex('kb',@str)-1)
print @val
if len(@str)-charindex('kb',@str)>1
select @str=substring(@str,charindex('kb',@str)+2 ,len(@str)-charindex('kb',@str))
else
select @atend=1
end


------------
tc at 12/10/2001 10:35:20 AM

I have data coming back like below

140 KB 8 KB 1450 KB

I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.