Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to reorder digits of string

Posted on 2007-11-14
3
Medium Priority
?
373 Views
Last Modified: 2010-03-19
I have a nvarchar field with 13 digits. all numbers like:

1234567654321

I need to run a query that will reorganize the order of this digits.

I need the 11th digit to me moved to number 13
I need the 12th digit to me moved to number 11
I need the 13th digit to me moved to number 12

How can this be done ?   Following such example the above number would end up being:

From: 1234567654321
To: 1234567654213

Thanks

Aleks
0
Comment
Question by:amucinobluedot
[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
3 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20283917
to look at the results before running it:
select yourfield, left(yourfield, 10) + substring(yourfield, 12,2) + substring(yourfield, 11,1) as new_value
from yourtable

to update:

update yourtable
  set yourfield = left(yourfield, 10) + substring(yourfield, 12,2) + substring(yourfield, 11,1)
0
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 400 total points
ID: 20283936
declare @var char(13)
select  @var = '1234567654321'
select substring(@var,1,10)+substring(@var,12,2)+substring(@var,11,1)

hope this helps ...
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 20285153

SELECT STUFF(yourField, 11, 1, '') + SUBSTRING(yourField, 11, 1)
FROM tableName

UPDATE tableName
SET yourField = STUFF(yourField, 11, 1, '') + SUBSTRING(yourField, 11, 1)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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