vengatesh Dotnet

Thursday, August 9, 2012

Split String And number from the group of string and number

This following example input integer must be placed beginning or end

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('Goyal1000')
Insert Into @Temp Values('BTA50')
Insert Into @Temp Values('BTA030')
Insert Into @Temp Values('BTA')
Insert Into @Temp Values('123')
Insert Into @Temp Values('X999')

Select Data,Left(Data, PatIndex('%[0-9]%', Data + '1') - 1), substring(data,len(Left(Data, PatIndex('%[0-9]%', Data + '1') - 1))+1,LEN(data))

From   @Temp

in case input value mixed like this 'dsfh343lkjdsfh33'

u can use this

Declare @s varchar(100),@result varchar(100)
set @s='as4khd0939sdf78'
set @result=''

select
    @result=@result+
            case when number like '[0-9]' then number else '' end from
    (
         select substring(@s,number,1) as number from
        (
            select number from master..spt_values
            where type='p' and number between 1 and len(@s)
        ) as t
    ) as t
select @result as only_numbers