Hi I am trying to reverse string word by word suppose I have input
I have created this code but it keeps running:
declare @I varchar(20)
declare @O varchar(20)
declare @T varchar(20)
set @I='My Name is sachin'
set @O=''
while CHARINDEX(' ',@I)>0
begin
set @T=substring(REVERSE(@I),1,charindex(' ',REVERSE(@I))-1)
set @O=@O+' '+REVERSE(@T)
set @I=reverse(STUFF(REVERSE(@I),1,CHARINDEX(' ',REVERSE(@I))-1,''))
end
Anyone can help me out?
Altered your query a little, try this.
declare @I varchar(20)
declare @O varchar(20)
declare @T varchar(20)
set @I='My Name is sachin'
set @O=''
while CHARINDEX(' ',@I)>0
begin
set @T=left(reverse(@i),charindex(' ',reverse(@i))-1)
set @O=@O+' '+REVERSE(@T)
set @I=left(@i,len(@i)-(1+len(@t)))
If CHARINDEX(' ',@I)=0
set @O=@O+' '+@i
select @o
end
'If' block is added to append the last word, as the 'Empty Space' will not be available to append the last word in the sentence.
Modified:
SELECT LTRIM(
(SELECT N' ' + x.display_term
FROM sys.dm_fts_parser('"Your Name is Georgel"', 0, 0, 0) AS x
ORDER BY x.occurrence DESC
FOR XML PATH('')))
in SQL Server
1) Function to split string copied from http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/
2) stuff the split string to one in reverse
CREATE FUNCTION [dbo].[usr_funSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX), pos smallint identity(1,1) -- added position field
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
select result = stuff((select ' ' + splitdata from dbo.[usr_funSplitString]('My Name is sachin',' ') order by pos desc
FOR XML PATH ('')), 1, 1, '')
Here you need customize split string UDF.Or you need split string UDF which return row number.
so i f you create UDF from don't use master..spt_values.use Number table
Try this,
declare @t table(col1 varchar(50))
insert into @t values ('My Name is sachin')
declare @Delimiter as varchar(10)=' '
;
WITH CTE
AS (
SELECT col1
,ca.number
FROM @t t
CROSS APPLY (
SELECT 1 number
UNION ALL
SELECT DISTINCT number + 1
FROM master..spt_values
WHERE number > 0
AND (SUBSTRING(col1, number, 1) = @Delimiter)
AND number <= len(col1)
) ca
)
,cteLen (
N1
,col1
,L1
)
AS (
SELECT s.number
,col1
,ISNULL(NULLIF(CHARINDEX(@Delimiter, col1, s.number), 0) - s.number, 4000)
FROM cte s
)
,CTE1
AS (
SELECT ItemNumber = ROW_NUMBER() OVER (
ORDER BY l.N1
)
,n1
,l1
,Item = SUBSTRING(col1, l.N1, l.L1)
FROM cteLen l
)
SELECT TOP 1 (
SELECT @Delimiter + item
FROM cte1
ORDER BY itemnumber DESC
FOR XML path('')
)
FROM cte1
One simple solution (but far from being perfect) is to use sys.dm_fts_parser function (reference here) thus:
SELECT LTRIM(
(SELECT N' ' + x.display_term
FROM sys.dm_fts_parser('"Your Name is Georgel", 0, 0, 0) AS x
ORDER BY x.occurence DESC
FOR XML PATH('')))
