[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

String Operations in SQL

Posted on 2006-11-22
2
Medium Priority
?
4,351 Views
Last Modified: 2008-02-20
Experts!!

I am using SQL 2000
I have a table called "MyTable" with a column called "hexcode". The data type is nvarchar (in a form of hex string).
The data looks some thing like this: 371D8041

I need a function that would re-arrange the above string so it looks like this: 41801D37
So basically I want to reverse the order of pair of charcters.

I am doing this:
select
  Lo1+Lo2+Lo3+Lo4 as ActualCode
from
(
 select
  Substring(Hexcode,7,2) Lo1, Substring(Hexcode,5,2) Lo2, Substring(Hexcode, 3,2) Lo3, Substring(Hexcode,1,2) Lo4
from
 MyTable
)
as T1

The above query works, but I need to write it as a function. This way I don't have to repeat the code every time.
How would I do this? Thanks
0
Comment
Question by:novice12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 17996152
create function dbo.ReverseHexString(@input varchar(40))
returns varchar(40)
as
  begin
   declare @res varchar(40)
   select @res = Substring(@input,7,2) +Substring(@input,5,2) +Substring(@input, 3,2) + Substring(@input,1,2)
   return (@res)
 end


and use that like this:

select hexcode, dbo.ReverseHexCode(hexcode) ActualCode from Mytable
0
 
LVL 39

Assisted Solution

by:appari
appari earned 400 total points
ID: 17996181
try like this

create function GetOrderedString(@srcVal nvarchar(8))
returns nvarchar(8)
as
begin
     return Substring((@srcVal ,7,2) + Substring((@srcVal ,5,2) + Substring((@srcVal , 3,2)+ Substring((@srcVal ,1,2)
end

and call it as follows

select dbo.GetOrderedString(Hexcode) from mytable
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question