Extracting the Integer

Question

Post reply

Extracting the Integer

acebass

SSC Veteran

Points: 279
More actions
December 20, 2002 at 3:28 am

#58657

Can Anyone Help?
I have a range of alphanumeric product codes
for example HBHORT023, HB894, H163A and I wish to extract the integer portion of these codes i.e 023, 894, 163. I know that I cannot use the INT() Function, is there any other way to acheive this in SQL.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

Hi, try this: -

declare @AlphaStr varchar(10)

,@RtnStr varchar(10)

,@i int

select @i = 1, @RtnStr = '',@AlphaStr = 'a2c123'

while @i <= len(@AlphaStr)

begin

if ascii(substring (@AlphaStr, @i,1)) between 48 and 57

begin

select @RtnStr = @RtnStr + substring (@AlphaStr, @i,1)

end

select @i = @i + 1

end

select @RtnStr

Regards,

Andy Jones

Edited by - andyj93 on 12/20/2002 03:44:11 AM

Edited by - andyj93 on 12/20/2002 03:44:42 AM

.

acebass SSC Veteran Points: 279 More actions · Answer 2

Thanks Andy, but how do I use this against all the product codes in the column in my Table (SQL6.5)

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 3

Hi, you could incooperate the logic above in a UDF, although in 6.5 not sure??

Regards,

Andy Jones

.

acebass SSC Veteran Points: 279 More actions · Answer 4

Can't do UDF's in 6.5. Could I do this in a stored procedure using a cursor maybe? and if so how do i do it?

Thanks in advance

Pete

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 5

Possible, but performance would be poor on a large table. You could maybe trigger the integer part into another column when inserting / updating or simply insert / update into another column if your data access is via stored procedures. These suggestions are no good of course if you are simply reporting on an existing database and have no control over the inserts.

Regards,

Andy Jones

.

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Or you can shorten a little by changing the while loop to this

while @i <= len(@AlphaStr)

select @RtnStr = @RtnStr + CASE WHEN substring (@AlphaStr, @i,1) LIKE '[0-9]' THEN substring (@AlphaStr, @i,1) ELSE '' END, @i = @i + 1

Because you have no outputs you can increment the value inside the select.

Also, if you need this in a SELECT statement and are using SQL 2000 you can make the process a function so you do not have to create a cursor.