Reverse alphanumeric string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reverse alphanumeric string

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-03 : 03:46:32

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)

17689 Posts

Posted - 2013-12-03 : 04:14:53

[code]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[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-12-04 : 02:13:53

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-04 : 07:25:43

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources