Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- dbo.RegexReplace( '.*?(d+).*', myField, '$1' )
- CAST( dbo.RegexReplace( '.*?(d+).*', myField, '$1' ) AS INT )
- create table #t ( val varchar(100) )
- insert #t select 983
- insert #t select 294
- insert #t select 'a343'
- insert #t select 'a3546f';
- GO
- ;with ValueRange as (
- select val,
- [from] = patindex('%[0-9]%', val),
- [to] = case patindex('%[a-z]', val)
- when 0 then len(val)
- else patindex('%[a-z]', val) - patindex('%[0-9]%', val)
- end
- from #t
- )
- select substring(val, [from], [to]) as val
- from ValueRange VR
- order by cast(substring(val, [from], [to]) as int) desc
- create FUNCTION [dbo].[getFirstNumeric](
- @s VARCHAR(50)
- )
- RETURNS int AS
- BEGIN
- set @s = substring(@s,patindex('%[0-9]%',@s),len(@s)-patindex('%[0-9]%',@s) + 1)
- if patindex('%[^0-9]%',@s) = 0
- return @s
- set @s = substring(@s,1,patindex('%[^0-9]%',@s)-1)
- return cast(@s as int)
- end
- select max(dbo.getFirstNumeric(yourColumn)) from yourTable
- SELECT MAX(dbo.parseVarcharToInt(column)) FROM table
- SELECT MAX(CAST(yourColumn AS int)) AS maxColumns FROM yourTable
- – Function to strip out non-numeric chars
- ALTER FUNCTION dbo.UDF_ParseNumericChars
- (
- @string VARCHAR(8000)
- )
- RETURNS VARCHAR(8000)
- AS
- BEGIN
- DECLARE @IncorrectCharLoc SMALLINT
- –SET @IncorrectCharLoc = PATINDEX(’%[^0-9A-Za-z]%’, @string)
- SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
- WHILE @IncorrectCharLoc > 0
- BEGIN
- SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
- SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
- END
- SET @string = @string
- RETURN @string
- END
- GO
- SELECT dbo.RegexReplace('[^0-9]', '','a5453b',1, 1)
- Select max(convert(int,SubString(VarName,PATINDEX('%[0-9]%',VarName),Len(VarName))))
- from ATable
Add Comment
Please, Sign In to add comment