Extract number from string
-
Hi.. My function is to extract only number from given string. i m using mysql database and i m new to the syntax. is there anyone help me pls? thanks in advance! ===================== BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1) END SET @Count = @Count + 1 END RETURN @IntNumbers ============================================= The following error Message is shown. Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@locator VARCHAR(20)) RETURNS INT AS BEGIN DECLARE @cnt INT DECLARE' at line 1
-
Hi.. My function is to extract only number from given string. i m using mysql database and i m new to the syntax. is there anyone help me pls? thanks in advance! ===================== BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1) END SET @Count = @Count + 1 END RETURN @IntNumbers ============================================= The following error Message is shown. Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@locator VARCHAR(20)) RETURNS INT AS BEGIN DECLARE @cnt INT DECLARE' at line 1
The error is not occuring in the code segment you posted. Your SQL apparently has an error of some type (SQL does not provide very meaningful messages!) near line 4 shortly before the code that starts with "@locator .... I see a double ")" in the varchar(20)) line, but SQL has not seen that error yet, so it may be just a typo in your posting or a future error in your code. If you still can't find the error, try posting the entire SQL so we can see the code in the area of the error message. Good Luck
-
The error is not occuring in the code segment you posted. Your SQL apparently has an error of some type (SQL does not provide very meaningful messages!) near line 4 shortly before the code that starts with "@locator .... I see a double ")" in the varchar(20)) line, but SQL has not seen that error yet, so it may be just a typo in your posting or a future error in your code. If you still can't find the error, try posting the entire SQL so we can see the code in the area of the error message. Good Luck
Hi... now i change my function like that. CREATE FUNCTION `peerreview`.`ExtractNumber`(locator VARCHAR(20)) RETURNS INT BEGIN DECLARE cnt INT; DECLARE pos VARCHAR(5); SET cnt=0; SET pos=''; WHILE cnt<=LEN(locator) BEGIN IF SUBSTR(locator,cnt,1)>='0'AND SUBSTR(locator,cnt,1)<='9' SET pos=CONCAT(pos,SUBSTR(locator,cnt,1)); END SET cnt=cnt+1; END RETURN CAST(pos AS INT) END the error show like that: MySQL Error Number 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN IF SUBSTR(locator,cnt,1)>='0' AND SUBSTR(locator,cnt,1)<' at line 9 ============= i would like to sort the rows depend on locator(locator1 and locator 2)fields.it also has other fields. E.g Before Sorting Locator 1 Locator 2 Page 20 Line 1 Page 10 Line 9 Page 3 Line 5 After Sorting it should be Locator 1 Locator 2 Page 3 Line 5 Page 10 Line 9 Page 20 Line 1 i can use dataview sort, but it can't get the correct order. (the result is like the following: Locator 1 Locator 2 Page 10 Line 9 Page 20 Line 1 page 3 Line 5 because it's like string sorting...) if there is a function that can extract only number from given string, i can use this function at table adapter's query. the result is binded with gridview. i am using mysql database and asp.net 2.0 with vs 2005. thanks for your suggestion.
-
Hi.. My function is to extract only number from given string. i m using mysql database and i m new to the syntax. is there anyone help me pls? thanks in advance! ===================== BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1) END SET @Count = @Count + 1 END RETURN @IntNumbers ============================================= The following error Message is shown. Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@locator VARCHAR(20)) RETURNS INT AS BEGIN DECLARE @cnt INT DECLARE' at line 1
Your While statement should use a "Do" instead of "Begin" and an "End While" instead of "end" You might take a look at a few samples on the net to get examples of SQL code. One that I have found useful is http://www.artfulsoftware.com/infotree/queries.php?&bw=1260[^] I am not sure exactly what you are doing, with your extract, but you might consider chopping the string into substrings and sorting the substrings. That way your numeric substring(s) should be at the top, (assuming your original string does not contain any special char's). Good Luck