DECLARE @input VARCHAR(MAX)
DECLARE @output VARCHAR(MAX)
DECLARE @lenght INT
SET @input = 'Gray,Dark'
SET @output = ''
WHILE LEN(@input) > 0
BEGIN
IF CHARINDEX(',', @input) > 0
BEGIN
SET @output = SUBSTRING(@input,0,CHARINDEX(',', @input)) + ',' + @output
SET @input = LTRIM(RTRIM(SUBSTRING(@input,CHARINDEX(',', @input)+1,LEN(@input))))
END
ELSE
BEGIN
SET @output = @input + ',' + @output
SET @input = ''
END
END
SELECT replace(@output, ',', ' ')
OR Create a function using the following:
CREATE FUNCTION dbo.GetOutput(@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @output VARCHAR(MAX)
DECLARE @lenght INT
SET @output = ''
WHILE LEN(@input) > 0
BEGIN
IF CHARINDEX(',', @input) > 0
BEGIN
SET @output = SUBSTRING(@input,0,CHARINDEX(',', @input)) + ',' + @output
SET @input = LTRIM(RTRIM(SUBSTRING(@input,CHARINDEX(',', @input)+1,LEN(@input))))
END
ELSE
BEGIN
SET @output = @input + ',' + @output
SET @input = ''
END
END
RETURN replace(@output, ',', ' ')
END
Then call the function whenever/wherever you need:
CREATE TABLE test (t_id INT,
Instr VARCHAR(1000))
INSERT INTO test
VALUES (1,'Gray,Dark'),
(2,'Blue,Dark'),
(3,'Blue Pink')
SELECT ISNULL(S.a.value('(/h/r)[2]', 'NVARCHAR(50)')+' '+ S.a.value('(/h/r)[1]', 'NVARCHAR(50)'), S.a.value('(/h/r)[1]', 'NVARCHAR(50)')) AS col
FROM (
SELECT t_id, Cast('<h><r>'+Replace(Instr,',','</r><r>')+'</r></h>' as XML) colXML FROM test) d
Cross Apply d.colXML.nodes('/h') S(a)
drop table test
CREATE FUNCTION dbo.split(
@delimited NVARCHAR(MAX), @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
solution for a particular word only
select ltrim((select ' ' + val from (
select id, val from dbo.split('Blue,Dark', ',')
) t order by id desc for xml path(''))) as color
solution if data is stored in table
declare @colors table( color varchar(100))
insert into @colors
select 'Blue,Dark' union all select 'Green,Light' union all select 'Purple,Dark'
;with cte as (
select id, s.val, color
from @colors c cross apply dbo.split(color, ',') s
)
select ltrim((select ' ' + val from cte t2 where t1.color = t2.color order by id desc for xml path(''))) as color
from cte t1
group by color
can I have the table valued function where I can pass the more than 1000 of rows of the table at time.
My table name is vehicle and there is color column which needs to be updated.
Hi
In my previous post, I provided the solution with sample to work with table also.
Use this solution for updating color as per your requirement
/*
CREATE FUNCTION dbo.split(
@delimited NVARCHAR(MAX), @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
*/
create table vehicle(vehicleid int, color varchar(100))
insert into vehicle
select 1, 'Blue,Dark' union all
select 2, 'Green,Light' union all
select 3, 'Purple,Dark'
select * from vehicle -- before update
;with cte as (
select vehicleid, id, s.val, color
from vehicle c cross apply dbo.split(color, ',') s
)
update vehicle set color = a.color
from (
select vehicleid, ltrim((select ' ' + val from cte t2 where t1.vehicleid = t2.vehicleid order by id desc for xml path(''))) as color
from cte t1
group by vehicleid
) a where vehicle.vehicleid = a.vehicleid
select * from vehicle -- after update
siyasangani
0 Points
4 Posts
reverse particular string in sql
Nov 09, 2012 08:47 PM|LINK
In sql server:
if a comma is present, remove the comma and reverse the order of the words. i.e we receive as "Blue,Dark." The site should show as "Dark Blue"
I first need to identify in sql if string has comma if it has comma I need to reverse the word.
for ex color on website is Gray,Dark we have to show it as Dark Gray.
I urgently need your help with this.
clevesteve
Participant
1405 Points
406 Posts
Re: reverse particular string in sql
Nov 09, 2012 08:52 PM|LINK
SELECT SUBSTRING(COLOR,CHARINDEX(',',COLOR)+1,LEN(COLOR)-CHARINDEX(COLOR)) & ' ' & SUBSTRING(COLOR,1,CHARINDEX(',',COLOR)-1) COLOR
monjurul hab...
Member
62 Points
11 Posts
Re: reverse particular string in sql
Nov 09, 2012 08:59 PM|LINK
Try following:
DECLARE @input VARCHAR(MAX) DECLARE @output VARCHAR(MAX) DECLARE @lenght INT SET @input = 'Gray,Dark' SET @output = '' WHILE LEN(@input) > 0 BEGIN IF CHARINDEX(',', @input) > 0 BEGIN SET @output = SUBSTRING(@input,0,CHARINDEX(',', @input)) + ',' + @output SET @input = LTRIM(RTRIM(SUBSTRING(@input,CHARINDEX(',', @input)+1,LEN(@input)))) END ELSE BEGIN SET @output = @input + ',' + @output SET @input = '' END END SELECT replace(@output, ',', ' ')OR Create a function using the following:
CREATE FUNCTION dbo.GetOutput(@input VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @output VARCHAR(MAX) DECLARE @lenght INT SET @output = '' WHILE LEN(@input) > 0 BEGIN IF CHARINDEX(',', @input) > 0 BEGIN SET @output = SUBSTRING(@input,0,CHARINDEX(',', @input)) + ',' + @output SET @input = LTRIM(RTRIM(SUBSTRING(@input,CHARINDEX(',', @input)+1,LEN(@input)))) END ELSE BEGIN SET @output = @input + ',' + @output SET @input = '' END END RETURN replace(@output, ',', ' ') ENDThen call the function whenever/wherever you need:
SELECT dbo.GetOutput('Gray,Dark')Habib
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: reverse particular string in sql
Nov 09, 2012 09:12 PM|LINK
CREATE TABLE test (t_id INT, Instr VARCHAR(1000)) INSERT INTO test VALUES (1,'Gray,Dark'), (2,'Blue,Dark'), (3,'Blue Pink') SELECT ISNULL(S.a.value('(/h/r)[2]', 'NVARCHAR(50)')+' '+ S.a.value('(/h/r)[1]', 'NVARCHAR(50)'), S.a.value('(/h/r)[1]', 'NVARCHAR(50)')) AS col FROM ( SELECT t_id, Cast('<h><r>'+Replace(Instr,',','</r><r>')+'</r></h>' as XML) colXML FROM test) d Cross Apply d.colXML.nodes('/h') S(a) drop table testFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
siyasangani
0 Points
4 Posts
Re: reverse particular string in sql
Nov 10, 2012 01:45 AM|LINK
this didn't help as it says charindex requires 3 argument.
can I have exact syntaz
siyasangani
0 Points
4 Posts
Re: reverse particular string in sql
Nov 10, 2012 01:54 AM|LINK
function works.
But how can i ustilize this function while updating color in vehicle table.
suppose I have application which updates color column in vehicle table in database.
example:
UPDATE c SET Color=CASE WHEN ISNULL(.InvtColor1Name,'')='' THEN c.Color ELSE vehicleColorName END
FROM vehicle
Before updating in database color info in vehicle table I need to apply this function so it remove comma if it exist and then shuffle words.
This is needed because color info we get from webservice will have that type of data so before we update it in our database it should be fixed.
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: reverse particular string in sql
Nov 10, 2012 03:27 AM|LINK
first create function
CREATE FUNCTION dbo.split( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN ENDsolution for a particular word only
select ltrim((select ' ' + val from ( select id, val from dbo.split('Blue,Dark', ',') ) t order by id desc for xml path(''))) as colorsolution if data is stored in table
Sandeep Mittal | My Blog - IT Developer Zone
siyasangani
0 Points
4 Posts
Re: reverse particular string in sql
Nov 12, 2012 12:12 AM|LINK
can I have the table valued function where I can pass the more than 1000 of rows of the table at time.
My table name is vehicle and there is color column which needs to be updated.
bhaskar.mule
Contributor
2270 Points
659 Posts
Re: reverse particular string in sql
Nov 12, 2012 01:05 AM|LINK
hi
refer this
http://csharpektroncmssql.blogspot.com/2012/05/sql-serverhow-to-reverse-string.html
Site:Rare technical solutions
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: reverse particular string in sql
Nov 12, 2012 03:38 AM|LINK
Hi
In my previous post, I provided the solution with sample to work with table also.
Use this solution for updating color as per your requirement
/* CREATE FUNCTION dbo.split( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN END */ create table vehicle(vehicleid int, color varchar(100)) insert into vehicle select 1, 'Blue,Dark' union all select 2, 'Green,Light' union all select 3, 'Purple,Dark' select * from vehicle -- before update ;with cte as ( select vehicleid, id, s.val, color from vehicle c cross apply dbo.split(color, ',') s ) update vehicle set color = a.color from ( select vehicleid, ltrim((select ' ' + val from cte t2 where t1.vehicleid = t2.vehicleid order by id desc for xml path(''))) as color from cte t1 group by vehicleid ) a where vehicle.vehicleid = a.vehicleid select * from vehicle -- after updateSandeep Mittal | My Blog - IT Developer Zone