reverse particular string in sql RSS

• • Reply
  

  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.

• • Reply
  

  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

  Not an IT Professional
• • Reply
  

  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, ',', ' ') 
  END

  Then call the function whenever/wherever you need:

  SELECT dbo.GetOutput('Gray,Dark')

  
  
  
  
  

  Thanks,
  Habib
  • Edited by monjurul habib on Nov 09, 2012 09:11 PM
• • Reply
  

  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 test

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  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

• • Reply
  

  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.

   

• • Reply
  

  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
  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by Chen Yu - MSFT on Nov 14, 2012 08:39 AM
  • Edited by Chen Yu - MSFT on Nov 14, 2012 08:40 AM
• • Reply
  

  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.

• • Reply
  

  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

  Bhaskar.Mule
  Site:Rare technical solutions
• • Reply
  

  sandeepmitta...

  Contributor

  6801 Points

  1059 Posts

  Re: reverse particular string in sql

  Nov 12, 2012 03:38 AM|LINK

  siyasangani

  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

  
  
  

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Nov 12, 2012 03:38 AM
  • Edited by sandeepmittal11 on Nov 12, 2012 03:39 AM
  • Marked as answer by Chen Yu - MSFT on Nov 14, 2012 08:40 AM