String Operations in SQL

Posted on 2006-11-22
Medium Priority
Last Modified: 2008-02-20

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:
  Lo1+Lo2+Lo3+Lo4 as ActualCode
  Substring(Hexcode,7,2) Lo1, Substring(Hexcode,5,2) Lo2, Substring(Hexcode, 3,2) Lo3, Substring(Hexcode,1,2) Lo4
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
Question by:novice12
LVL 143

Accepted Solution

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

and use that like this:

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

Assisted Solution

appari earned 400 total points
ID: 17996181
try like this

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

and call it as follows

select dbo.GetOrderedString(Hexcode) from mytable

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 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