SQL function?

Question

EI guys im new with SQL , is there a function in SQL that could extract number in a string ? e.g "cs 29" is the string and i would only need to get "29" trim wont work because the number may appear before or after the letters.

Answer 1

While there is not a function that does this specifically, you can create your own user defined function which performs this. Basically you can use a WHILE loop (I'm assuming you are using Transact-SQL) starting from the first character to the LEN of the string, then use LEFT to extract one character from your current postion.



You can use ISNUMERIC to test the character and if it is, store the starting postion. Continue your loop until you reach the end of the string or you find a character that no longer satisfies the ISNUMERIC.



With the starting and ending positions, you can then use SUBSTRING to pull out the numeric portion of the result.

Answer 2

A database is more than just a collection of data. A proper DBMS (e.g., MySQL) offers things like redundancy and fault-tolerance. If you are trying to use CSV to replace a DBMS, you are setting yourself up for problems down the road. If your host charges you $1000/year for a simple SQL database, get a different host. You are being scammed. OK, since you can't get a different host, you can still get a SQL server on the cheap. You can get a cheap MySQL/Postresql host for a few bucks a month. I don't know of any offhand, but it won't be terribly hard to find in a search. You can then use that server instead of whatever one costs $1000/year. If you need any help configuring that, it probably won't be hard to find some student at the institution to help you.

Answer 3

well, it'll help if you tell us a bit more, like.... what backend language are you using?



You accessing it though PHP? ASP? VB? Raw socket???



As for the SQL language itself, theres no built in thing that lets you phrase strings like that. You can only do it third party.



Also, if you let us know what your trying to do with the number, then we might know a way around it.

Answer 4

It depends on which DBMS you're using. The SQL92 standard for example has a translate() function which can be used to "remove" unwanted characters. Oracle and others support this function.



So, as an example in Oracle:



SQL> select

translate('ABC123DEF', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' ') from dual;



TRAN

----

123



SQL>

Answer 5

no such Sql functions r available

Answer 6

Please refer following link for your answer. Function is there :

http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/