October 13, 2011 at 7:39 am
Hi,
I got a table which has contents like
TbaleA
Col
100
100A1
1000
1000B1
1000C1
200D1
Now i want to have result some thing like
100
100
1000
1000
1000
200
I know we can use Replace function to Replace a particular character but how to Replace the string from the words B or C or D with a blank
October 13, 2011 at 8:43 am
tripri (10/13/2011)
Hi,I got a table which has contents like
TbaleA
Col
100
100A1
1000
1000B1
1000C1
200D1
Now i want to have result some thing like
100
100
1000
1000
1000
200
I know we can use Replace function to Replace a particular character but how to Replace the string from the words B or C or D with a blank
You could use patindex:
declare @test-2 table (id int identity(1,1) primary key,
data varchar(20))
insert into @test-2 values('100')
insert into @test-2 values('100A1')
insert into @test-2 values('1000')
insert into @test-2 values('1000B1')
insert into @test-2 values('1000C1')
insert into @test-2 values('200D1')
select patindex('%[a-z]%',data), SUBSTRING(data,1,case when patindex('%[a-z]%',data) = 0 then len(data) else patindex('%[a-z]%',data)-1 end) from @test-2
October 13, 2011 at 9:10 am
I understand that you want cut off all trailing characters starting from the first not numeric. I emulated a vb function Val using this code:
Create function [dbo].[Val]
(
@text nvarchar(40)
)
returns float
as begin
-- emulate vba's val() function
declare @result float
declare @Tmp varchar(40)
set @Tmp = @text
while isnumeric(@tmp) = 0 and len(@tmp)>0
begin
set @Tmp=left(@tmp,len(@tmp)-1)
end
set @result = cast(@tmp as float)
return @result
end
-- CHECK the function
------declare @t nvarchar(40)
------set @t='2iop23'
------select [dbo].[Val] (@t)
October 13, 2011 at 9:16 am
Thank You so much Guys:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply