How to split a string with specific words in sql server

CREATE function [dbo].[SplitByString]
(
@string nvarchar(4000),
@delimiter nvarchar(10),
@addDelimetrContent bit
)
returns @table table
(
[Value] nvarchar(4000)
)
begin
declare @nextString nvarchar(4000)
declare @pos int, @nextPos int

SET @nextString = ''
SET @string = @string + @delimiter

SET @pos = CHARINDEX(@delimiter, @string)
SET @nextPos = 1
WHILE (@pos <> 0)
BEGIN

IF (@addDelimetrContent = 1)
SET @nextString = @delimiter + SUBSTRING(@string, 1, @pos - 1)
ELSE
SET @nextString = SUBSTRING(@string, 1, @pos - 1)

IF (@nextString != '' AND @nextString != @delimiter)
BEGIN
INSERT INTO @table ([Value])
VALUES (@nextString)
END
SET @string = SUBSTRING(@string, @pos + LEN(@delimiter), LEN(@string))
SET @nextPos = @pos
SET @pos = CHARINDEX(@delimiter, @string)
END
RETURN
END

Ex : select * from dbo.[SplitByString]('http://www.google.comhttp://gmail.comhttp://yahoo.com','http',1)

Note : Make Sure that if @addDelimetrContent = 1 then we will add delimeter string to each row otherwise will add as empty.

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