August 26, 2008 at 5:02 am
i need a query that make
'A010' + 1 = 'A011' or
'ABC13456' + 12 = 'ABC13468'
how can i do this
August 26, 2008 at 7:24 am
Dear Friend
Following procdure will help you to get your result.
Create Procedure ADD_NUMBER_TO_STRING
(
@Value varchar(20),
@AddNum int
)
AS
--===============================
--Question Is Described As below
--i need a query that make
--'A010' + 1 = 'A011' or
'ABC13456' + 12 = 'ABC13468'
--==================================
--Declare variables to be used
Declare @NumPart int
Declare @Counter int
Declare @Length int
Declare @Var as varchar(1)
Declare @NumStart int
--Create Temperory Table
Create table Tbl_Temp(AlphaBet varchar(1))
--Insert Alphabets and 0 for number start with 0
Insert Into Tbl_Temp values('0')
Insert Into Tbl_Temp values('A')
Insert Into Tbl_Temp values('B')
Insert Into Tbl_Temp values('C')
Insert Into Tbl_Temp values('D')
Insert Into Tbl_Temp values('E')
Insert Into Tbl_Temp values('F')
Insert Into Tbl_Temp values('G')
Insert Into Tbl_Temp values('H')
Insert Into Tbl_Temp values('I')
Insert Into Tbl_Temp values('J')
Insert Into Tbl_Temp values('K')
Insert Into Tbl_Temp values('L')
Insert Into Tbl_Temp values('M')
Insert Into Tbl_Temp values('N')
Insert Into Tbl_Temp values('O')
Insert Into Tbl_Temp values('P')
Insert Into Tbl_Temp values('Q')
Insert Into Tbl_Temp values('R')
Insert Into Tbl_Temp values('S')
Insert Into Tbl_Temp values('T')
Insert Into Tbl_Temp values('U')
Insert Into Tbl_Temp values('V')
Insert Into Tbl_Temp values('W')
Insert Into Tbl_Temp values('X')
Insert Into Tbl_Temp values('Y')
Insert Into Tbl_Temp values('Z')
--
Set @NumStart = 0
Set @Counter = 1
--Set @AlphaPart = substring(@Value,1,2)
--Set @NumPart = Substring(@Value,2,3)
--Seth Length of string
Set @Length = len(@Value)
while (@Counter <= @Length)
Begin
Set @Var = Substring(@Value, @Counter, @Counter + 1)
if( @Var = (Select Alphabet from Tbl_Temp where Alphabet = @Var))
Begin
Set @Counter = @Counter + 1
End
Else
Begin
Set @NumStart = @Counter
Break;
End
End
Set @NumPart = Substring(@Value, @NumStart ,@Length)
Set @Value = Substring(@Value,1,@NumStart - 1)
Set @NumPart = @NumPart + @AddNum
Set @Value = @Value + cast(@NumPart as varchar(20))
--Print Required result
Select 'Final Value : '+ @Value
--Drop Temperory Table
Drop Table Tbl_Temp
--=================
--Test : Execute Following Just Select Following Line and Execute
--Exec ADD_NUMBER_TO_STRING 'A010',1
--Exec ADD_NUMBER_TO_STRING 'ABC13456',12
Cheers,
Saurabh Singh
August 26, 2008 at 8:39 am
Thats good saurabh.k.singh.
May I suggest that if you changed the declaration @NumPart from int to float, and @AddNum from int to float, then you will be able to add decimal figures as well :D.
EDIT:
Actually, that only allows one decimal place at the moment.
August 26, 2008 at 1:48 pm
thank's for help saurabh.k.singh
you are great
August 28, 2008 at 1:28 am
Hi there,
I have another solution. But this one also accepts symbols or any other characters EXCEPT numbers.
I'm not saying the other post was wrong. Actally I really like the post, very helpful and you can also use that sproc to do the same as mine by inputing numbers into the tables and making it do the opposite when filtering.
Oh yeah, i forgot to make this into decimal type but if you make it decimal, I think it will work fine... except for the last IF STATEMENT. But I think you can already alter it yourself.
Hope this helps
CREATE PROC AddToCodeNumber
@codeVARCHAR(50),
@numberINT
AS
BEGIN
DECLARE @LengthINT,
@iINT
SELECT@Length = len(@code),
@i = 0
WHILE (SELECT SUBSTRING(@code,@length-@i,1)) IN ('1','2','3','4','5','6','7','8','9','0')
BEGIN
SELECT @i=@i+1
END
SELECT @number = RIGHT(@code,@i) + @number
IF LEN(@number)=LEN(RIGHT(@code,@i))
BEGIN
SELECT LEFT(@code,@Length-@i) + CAST(@number AS VARCHAR(MAX))
END
ELSE
BEGIN
SELECT 'ERROR'
END
END
RETURN
GO
EXEC AddToCodeNumber
@code='ABC/*-+32100',
@number=212
EXEC AddToCodeNumber
@code='ABC/*-+32100',
@number=2122346
Tell me if this was helpful ^__^ Thanks!
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 29, 2008 at 5:10 am
Try this version using a Tally table. No loops, no cursors.
IF OBJECT_ID ( 'tempdb..#Tally' ) IS NOT NULL
BEGIN
DROP TABLE #Tally
END
GO
DECLARE @AlphaNumber varchar ( 20 )
DEcLARE @numtoadd decimal ( 20, 4 )
DECLARE @NumPart decimal ( 20, 4 )
DECLARE @Result varchar ( 20 )
DECLARE @strlen int
DECLARE @numpos int
SET @AlphaNumber = 'ABC12345.6789'
SET @numtoadd = 0.3211
SET @strlen = DATALENGTH ( @AlphaNumber )
SELECT TOP ( @strlen )
IDENTITY ( int, 1, 1 ) AS N
INTO #Tally
FROM master.dbo.syscolumns
ALTER TABLE #Tally
ADD C char ( 1 )
UPDATE #Tally SET C = SUBSTRING ( @AlphaNumber, N, 1 )
--SELECT * FROM #Tally
SELECT @numpos = MIN ( N ) FROM #Tally WHERE C BETWEEN '0' AND '9' OR C = '.'
--PRINT @numpos
SET @NumPart = CAST ( SUBSTRING ( @AlphaNumber, @numpos, @strlen ) AS decimal ( 20, 4 ) )
--PRINT @NumPart
SET @NumPart = @NumPart + @numtoadd
SET @Result = LEFT ( @AlphaNumber, @numpos - 1 ) + LTRIM ( STR ( @NumPart, @strlen, 4 ) )
PRINT @AlphaNumber + ' + ' + LTRIM ( STR ( @numtoadd, 10, 4 ) ) + ' = ' + @Result
DROP TABLE #Tally
I know, it breaks if there is no numeric part or there is more than one decimal point 🙂
Regards,
Jan
August 29, 2008 at 5:34 am
Hi,
try the below one.
DECLARE @Text VARCHAR(16)
SET @Text = 'ABC222'
SELECT SUBSTRING(@Text,PATINDEX('%[0-9]%',@Text),LEN(@Text)) + 100
---
August 29, 2008 at 6:00 pm
Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.
create table #tester
(seq tinyint null,
txt varchar(20) null,
num integer null)
set nocount on
insert #tester values (1,'ABC44',10)
insert #tester values (2,'ABC44',0)
insert #tester values (3,'',10)
insert #tester values (4,'ABC4',10)
insert #tester values (5,'ABC44',1000)
insert #tester values (6,'ABC',10)
insert #tester values (7,null,10)
insert #tester values (8,'ABC',null)
insert #tester values (9,null,null)
set nocount off
select seq,
case when isnull(txt,'') = '' then isnull(convert(varchar,num),'')
when patindex('%[0-9]%',txt) = 0 then txt + isnull(convert(varchar,num),'')
else substring(txt,1,patindex('%[0-9]%',txt) - 1) +
convert(varchar,convert(int,substring(txt, patindex('%[0-9]%',txt),len(txt)) + isnull(num,0)))
end as 'result'
from #tester
order by seq
drop table #tester
/*
seq result
---- --------------------------------------------------
1 ABC54
2 ABC44
3 10
4 ABC14
5 ABC1044
6 ABC10
7 10
8 ABC
9
(9 row(s) affected)
*/
August 30, 2008 at 3:54 pm
sqluser (8/29/2008)
Hi,try the below one.
DECLARE @Text VARCHAR(16)
SET @Text = 'ABC222'
SELECT SUBSTRING(@Text,PATINDEX('%[0-9]%',@Text),LEN(@Text)) + 100
---
Doesn't return the "ABC" part... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2008 at 4:03 pm
Mike Mullen (8/29/2008)
Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.
Yes, it does. Nicely done! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2008 at 4:05 pm
Just a thought... why not keep the alpha and numeric portions separate and combine them only in a calculated column??? Then you wouldn't have to mess with all of this. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply