Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Jun 24th, 2018 52 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  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