SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to pick alphabet in a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 08/09/2013 :  03:50:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 08/09/2013 :  05:59:14  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 08/12/2013 :  02:42:58  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 08/16/2013 :  13:26:09  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000