How to get the numeric part from a string using T-SQL?
select left(@str, patindex('%[^0-9]%', @str+'.') - 1)
Query to get only numbers from a string
First create this UDF
CREATE FUNCTION dbo.udf_GetNumeric
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
Now use the function
as
SELECT dbo.udf_GetNumeric(column_name)
from table_name
SQL FIDDLE
I hope this solved your problem.
Reference
SQL take just the numeric values from a varchar
Here's the example with PATINDEX:
select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))
This assumes (1) the field WILL have a numeric, (2) the numerics are all grouped together, and (3) the numerics don't have any subsequent characters after them.
T-SQL Extract Numbers from a string and everything in between
You can use charindex()
with patindex()
:
select substring(col, patindex('%[0-9]%', col),
len(col) - charindex(' ', col, patindex('%[0-9]%', col)) - 1
) as col
How to extract numbers from a string using TSQL
Since you have stable text and only 2 elements, you can make good use of replace and parsename:
declare @string varchar(100) = 'TEST RESULTS\TEST 1\RESULT 2'
select cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 2) as int) as Test
, cast(parsename(replace(replace(@string, 'TEST RESULTS\TEST ', ''), '\RESULT ', '.'), 1) as int) as Result
/*
Test Result
----------- -----------
1 2
*/
The replace portion does assume the same text and spacing always, and sets up for parsename with the period.
SQL How to extract numbers from a string?
Use a combination of Substr
& instr
SELECT Substr (textstring, 1,instr(textstring,' ') - 1) AS Output
FROM yourtable
Result:
OUTPUT
666
12345
Use this if you have text at the beginning e.g. aa12345 devils number is my PIN, that is 6666.
as it utilises the REGEXP_REPLACE
function.
SELECT REGEXP_REPLACE(Substr (textstring, 1,instr(textstring,' ') - 1), '[[:alpha:]]','') AS Output
FROM yourtable
SQL Fiddle: http://sqlfiddle.com/#!4/8edc9/1/0
Finding out just the numeric part from string and storing it as decimal datatype
If there can be only one such number in a string and if it always starts with a digit (i.e. there are no values omitting the 0 before the decimal like '.75'
) and ends with a digit, you can find the first digit by applying patindex()
to the string (as you already do) and the last digit by applying patindex()
to the reverse()
of the string.
SELECT convert(decimal(3, 1),
substring(column_desc,
patindex('%[0-9]%',
column_desc),
len(column_desc)
- patindex('%[0-9]%',
column_desc)
- patindex('%[0-9]%',
reverse(column_desc))
+ 2)) new_value
FROM elbat;
db<>fiddle
SQL: Extract numbers from string using Patindex-function
According to your sample data and expected results, you can get it with some relatively simple expressions.
SELECT A,
SUBSTRING( A, 0, ISNULL(NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A)+1)),
SUBSTRING( A, NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A))
FROM #Test;
For the first column, I start with 0 to avoid substracting 1 to the first digit position. I then use a combination of ISNULL(NULLIF(,0)LEN(A))
to assign the total length in case there are no digits available.
For the second columns, I still use NULLIF
, but I don't use ISNULL
to keep the value as NULL. I then just add the total length of the string.
Related Topics
Hive Select Data into an Array of Structs
Percent to Total in Postgresql Without Subquery
How to Specify a Password to 'Psql' Non-Interactively
Mysql Count() to Return 0 If No Records Found
Calculate Total Working Hour of Employee in SQL With Only 1 Column
Sql - How to Sum/Aggregate Certain Rows in a Table
How to Get Depatment Wise Max Salary as Well as Name of Employee Having It
Selecting the First Day of the Month in Hive
How to Extract Year from Hire_Date in My SQL
Multiple Insert With Same Column (Ms Access)
Selecting First and Last Values in a Group
Datediff Getting the Previous Month