daily pastebin goal
1%
SHARE
TWEET

Untitled

a guest Jun 24th, 2018 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. dbo.RegexReplace( '.*?(d+).*', myField, '$1' )
  2.    
  3. CAST( dbo.RegexReplace( '.*?(d+).*', myField, '$1' ) AS INT )
  4.    
  5. create table #t ( val varchar(100) )
  6. insert #t select 983
  7. insert #t select 294
  8. insert #t select 'a343'
  9. insert #t select 'a3546f';
  10. GO
  11.  
  12. ;with ValueRange as (
  13.     select  val,
  14.         [from] = patindex('%[0-9]%', val),
  15.         [to] = case patindex('%[a-z]', val)
  16.             when 0 then len(val)
  17.             else patindex('%[a-z]', val) - patindex('%[0-9]%', val)
  18.                end
  19.     from    #t
  20. )
  21. select  substring(val, [from], [to]) as val
  22. from    ValueRange VR
  23. order by cast(substring(val, [from], [to]) as int) desc
  24.    
  25. create FUNCTION [dbo].[getFirstNumeric](
  26.     @s VARCHAR(50)
  27. )  
  28. RETURNS int AS
  29. BEGIN
  30.  
  31. set @s = substring(@s,patindex('%[0-9]%',@s),len(@s)-patindex('%[0-9]%',@s) + 1)
  32. if patindex('%[^0-9]%',@s) = 0
  33.     return @s
  34. set @s = substring(@s,1,patindex('%[^0-9]%',@s)-1)
  35.  
  36. return cast(@s as int)
  37. end
  38.    
  39. select max(dbo.getFirstNumeric(yourColumn)) from yourTable
  40.    
  41. SELECT MAX(dbo.parseVarcharToInt(column)) FROM table
  42.    
  43. SELECT MAX(CAST(yourColumn AS int)) AS maxColumns FROM yourTable
  44.    
  45. – Function to strip out non-numeric chars
  46. ALTER FUNCTION dbo.UDF_ParseNumericChars
  47. (
  48. @string VARCHAR(8000)
  49. )
  50. RETURNS VARCHAR(8000)
  51. AS
  52. BEGIN
  53. DECLARE @IncorrectCharLoc SMALLINT
  54. –SET @IncorrectCharLoc = PATINDEX(’%[^0-9A-Za-z]%’, @string)
  55. SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
  56. WHILE @IncorrectCharLoc > 0
  57. BEGIN
  58. SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
  59. SET @IncorrectCharLoc = PATINDEX(’%[^0-9.]%’, @string)
  60. END
  61. SET @string = @string
  62. RETURN @string
  63. END
  64. GO
  65.    
  66. SELECT  dbo.RegexReplace('[^0-9]', '','a5453b',1, 1)
  67.    
  68. Select  max(convert(int,SubString(VarName,PATINDEX('%[0-9]%',VarName),Len(VarName))))
  69. from ATable
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top