How to get Only Numerics from a string in sql server

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


Ex : select dbo.[udf_GetNumeric]('xyz @abc123')

Comments

Post a Comment

Popular posts from this blog

How to Convert Word Document to PDF using C#

  Document wordDocument = null;             string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];             string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];             DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);             FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");             foreach (FileInfo wordFile in wordFiles)             {                 object paramSourceDocPath = (Object)wordFile.FullName;                 //(Object)wordFile.Na...
Read more

Sql query to get counts by quarterly and half yearly

---- Quarterly  SELECT     CASE         WHEN MONTH(date_column) IN (1, 2, 3) THEN 'Q1'         WHEN MONTH(date_column) IN (4, 5, 6) THEN 'Q2'         WHEN MONTH(date_column) IN (7, 8, 9) THEN 'Q3'         WHEN MONTH(date_column) IN (10, 11, 12) THEN 'Q4'     END AS quarter,     CONCAT(YEAR(date_column),         CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN '-H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN '-H2'         END) AS half_year,     COUNT(*) AS count FROM your_table GROUP BY quarter, half_year ORDER BY quarter, half_year; --- Half Yearly  SELECT YEAR(d...
Read more

How to upload File to Secured FTP ( SFTP ) in C#

Upload File from Local Machine to SFTP Server in C# The following C# code will upload a file from local machine to SFTP server. string _ftpURL = "testsftp.com"; //Host URL or address of the SFTP server string _UserName = "admin"; //User Name of the SFTP server string _Password = "admin123"; //Password of the SFTP server int _Port = 22; //Port No of the SFTP server (if any) string _ftpDirectory = "Receipts"; //The directory in SFTP server where the files will be uploaded string LocalDirectory = "D:\\FilePuller"; //Local directory from where the files will be uploaded string FileName = "test.txt"; //File name, which one will be uploaded Sftp oSftp = new Sftp(_ftpURL, _UserName, _Password); oSftp.Connect(_Port); oSftp.Put(LocalDirectory + "/" + FileName, _ftpDirectory + "/" + FileName); oSftp.Close();   tamir.sharpssh.dll can be downloaded from below link. https://sourcef...
Read more