This method can be used to query records with fixed numbers in a field.
This method can be used to query records with fixed numbers in a field.
The code is as follows:
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, '') -- deletes a non-numeric character, the loop ends. The remaining part is the number.
END
Return convert (bigint, @ No)
END
This function returns the numeric part of a string.
The PATINDEX function returns the index that appears for the first time in the string.
The STUFF function deletes characters of the specified length and inserts a group of characters at the specified start point.
Select F_Get_No ('sdsdf2334sfsd234 '), and '1234' is returned'
Note: This method can be used to query records whose numbers are fixed values in a field, and the letters are fixed values.