help with function to string out certain asci characters 1

[neemi]

Hello there...

I am trying to write a function where I can pass a string and it returns the numbers only based on the asci codes but I am getting an error.

The function so far is as below.

Can anyone help with what could be wrong.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ExtractInteger](@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
	If Asc(substring(@String, @Count, 1,)) < 48 
	or Asc(substring(@String, @Count, 1)) > 57 
		BEGIN   
		SET @IntNumbers = Left(@String, @Count - 1) & substring(@String, @Count + 1,1)
		END
	SET @Count = @Count + 1
END
RETURN @IntNumbers
END

SQL server says.... Incorrect syntax near the keyword 'Asc'. but am just not sure what the prob is.

Cheers in advance.
nimi

 

[gmmastros]

There are several problems here.

ASC is used to control sort order (Ascending). I think you want to use the ASCII function instead.

Your first string string has an extra comma in the parameter list.

With T-SQL [!]&[/!] is a boolean AND operator. You cannot use it for string concatenation.

Just fixing the syntax errors....

ALTER FUNCTION [dbo].[ExtractInteger](@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
    If Ascii(substring(@String, @Count, 1)) < 48
    or Ascii(substring(@String, @Count, 1)) > 57
        BEGIN   
        SET @IntNumbers = Left(@String, @Count - 1) + substring(@String, @Count + 1,1)
        END
    SET @Count = @Count + 1
END
RETURN @IntNumbers
END

I ran this code and got some unexpected results. If this does not return correct data for you, please post some inputs and output and I'll take another look.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[neemi]

Hi there What I want to do is remove any characters from a string and return the numbers. Also removing any leading zero's from the string.

ie. 000165700 should return 165700
ie. 0262 62727 should return 26262727
ie. 6271g-882088 should return 6271882088

I have the following so far.

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position 
-- and for the character string.
DECLARE @position int, @string char(15),@NewString char(15),@TempString char(15)
-- Initialize the variables.
SET @position = 1
SET @string = '056658re0568'
SET @NewString = ''
SET @TempString = ''

WHILE @position <= len(@string)
 begin  
	set @TempString = CHAR(AscII(substring(@String, @position, 1)))
	If  AscII(substring(@String, @position, 1)) >= 48 
		AND  AscII(substring(@String, @position, 1)) <= 57 

	BEGIN
		
		SET @NewString = @NewString + @TempString 	   SELECT @TempString,@NewString,@position AS POS,@NewString AS NS,ASCII(SUBSTRING(@string, @position, 1)),
		  CHAR(ASCII(SUBSTRING(@string, @position, 1))),Left(@String, @position-1),substring(@String, @position ,1)
		
   END

SET @position = @position + 1
end
SET NOCOUNT OFF
GO

 

[neemi]

but still can't get it right!!!

 

[gmmastros]

try this:

Alter FUNCTION [dbo].[ExtractInteger](@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
	  If Ascii(SubString(@String, @Count, 1)) Between 48 and 57
        BEGIN   
        SET @IntNumbers = @IntNumbers + substring(@String, @Count,1)
        END
    SET @Count = @Count + 1
END

Set @IntNumbers = Replace(LTrim(Replace(@IntNumbers, '0', ' ')), ' ', '0')

RETURN @IntNumbers
END

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[neemi]

Thank you. Works Great!!! :-D
And the solution seems so much simplier than the path i was taking.