Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to pick alphabet in a string

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-08-09 : 03:50:26
i need to increment values

i have written this script execute this :
this is for this scenario
Insert Into #String SELECT 'S601-S630',1,'Desc1'

Drop table #IDs
CREATE TABLE #IDs(ID INT IDENTITY(1,1) ,String VARCHAR(100),Flag Int,Description VARCHAR(1000))
CREATE TABLE #string(ID INT IDENTITY(1,1) ,String VARCHAR(100),Falg Int,Descript VARCHAR(1000))

DECLARE @min INT ,@Max INT ,@String VARCHAR(50),@Start VARCHAR(50),@End VARCHAR(50),@Flag INT,@Desc VARCHAR(1000)
DECLARE @CharIndex CHAR(1),@FinalString VARCHAR(100)


Insert Into #String SELECT 'S601-S630',1,'Desc1'
--Insert Into #String SELECT '800T-900T',2,'desc2'
--Insert Into #String SELECT '901-1200',3,'desc3'


SELECT
@min = MIN(Id) ,@Max= MAX(Id)
FROM
#string

WHILE @min < = @Max
BEGIN
SELECT
@String = String,
@Desc = Descript,
@Flag = Falg
FROM
#string WHERE id = @min

--SET @Start = SUBSTRING(@String,1,CHARINDEX('-',@String)-1)
--SET @ENd = SUBSTRING(@String,CHARINDEX('-',@String)+1,LEN(@String))

SET @CharIndex = CAST(SUBSTRING(@String,1,1) AS VARCHAR)

IF ISNUMERIC(@CharIndex) = 0
BEGIN

SET @String = REPLACE(@String,@CharIndex,'')
--SELECT @String
SET @Start = SUBSTRING(@String,1,CHARINDEX('-',LTRIM((RTRIM(@String))))-1)
--SELECT @Start
SET @ENd = SUBSTRING(@String,CHARINDEX('-',LTRIM((RTRIM(@String))))+1,LEN(LTRIM(RTRIM(@String))))
--SELECT @ENd
END



WHILE CAST(@Start AS INT) < = CAST(@ENd AS INT)
BEGIN
SET @Start = @CharIndex+ @Start
INSERT INTO #IDs
SELECT @Start,@Flag,@Desc
SET @Start = CAST(REPLACE(@Start,@CharIndex,'') AS INT)
SET @Start = @Start+1

END
SET @min = @min+1
END



Select * from #IDs --ORDER BY id
Drop table #string


how i need to increment if the input is like this

Insert Into #String SELECT '6S01-6S30',1,'Desc1'

if alphabet is in middle of the number??

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-09 : 05:59:14
sounds like effort is to generate custom sequence...

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-08-12 : 02:42:58
Hi visakh, this is not at all my requirement i posted the query which gives result like 'S601-S630'
result is S601,S602....S630.In my case how can i manage for '6S01-6S30' it need to give result like 6S01,6S02,6S03....6S30 etc...suggest me

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-16 : 13:26:09
quote:
Originally posted by mohan123

Hi visakh, this is not at all my requirement i posted the query which gives result like 'S601-S630'
result is S601,S602....S630.In my case how can i manage for '6S01-6S30' it need to give result like 6S01,6S02,6S03....6S30 etc...suggest me

P.V.P.MOhan


The easiest way is to make the column computed based on an identity column as explained in the link.
Otherwise you need a procedure to do the auto incremental part.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -