A statement that gets the number portion of a string in SQL _mssql

Copy Code code as follows:

The CREATE FUNCTION dbo. F_get_no
(
@No varchar (100)
)
RETURNS bigint
As
BEGIN
While PATINDEX ('%[^0-9]% ', @No) >0
BEGIN
SET @No =stuff (@No, PATINDEX ('%[^0-9]% ', @No), 1, ')--delete a non-numeric character, the loop ends, and the remainder is part of a number
End
Return CONVERT (bigint, @No)
End

This function can return the number part of a string
Patindex function returns the index of the first occurrence of the content in the string
The stuff function deletes the specified length of characters and inserts another set of characters at the specified starting point.
Select F_get_no (' sdsdf2334sfsd234 ') returns ' 2334234 '
Note: This method can solve the query of a field in the number of fixed values of the record, the letter part of the value is similar