SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Reverse alphanumeric string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 12/03/2013 :  03:46:32  Show Profile  Reply with Quote
Hi,

I have a field with data and some contain numbers and letters while others contain only numbers or only letters.
I need to reverse only the letters in the string and not the numbers.
Reverse function reverses everything.

5-god123 => 5-dog123

How can i do it? Do I need patindex and substring?

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 12/03/2013 :  04:14:53  Show Profile  Reply with Quote
declare	@str	varchar(100),
	@out	varchar(100),
	@alpha	varchar(100),
	@i	int

select	@str	= '5-god123'

select	@i	= 1,
	@out	= '',
	@alpha	= ''

while	@i <= len(@str)
begin
	if	substring(@str, @i, 1) not between 'a' and 'z'
	begin
		select	@out	= @out + reverse(@alpha) + substring(@str, @i, 1)
		select	@alpha	= ''
	end
	else
		select	@alpha	= @alpha + substring(@str, @i, 1)

	select	@i	= @i + 1
end

select	@out



KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/04/2013 :  02:13:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Another method
declare	@str	varchar(100),
	@out	varchar(100),
	@alpha	varchar(100),
	@i	int

select	@str	= '5-god123'
select @str,
	substring(@str,1,patindex('%[a-zA-Z]%',@str)-1)+
	reverse(substring(@str,patindex('%[a-zA-Z]%',@str),len(@str)-patindex('%[a-zA-Z]%',reverse(@str))-1))+
	right(@str,patindex('%[a-zA-Z]%',reverse(@str))-1)


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 12/04/2013 02:14:33
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 12/04/2013 :  07:25:43  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Another method
declare	@str	varchar(100),
	@out	varchar(100),
	@alpha	varchar(100),
	@i	int

select	@str	= '5-god123'
select @str,
	substring(@str,1,patindex('%[a-zA-Z]%',@str)-1)+
	reverse(substring(@str,patindex('%[a-zA-Z]%',@str),len(@str)-patindex('%[a-zA-Z]%',reverse(@str))-1))+
	right(@str,patindex('%[a-zA-Z]%',reverse(@str))-1)


Madhivanan

Failing to plan is Planning to fail



try with "5-god123-abc" and it will be result in "5-cba-321dog"



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000