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
Medium Priority
373 Views
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
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

LVL 143

Assisted Solution

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

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

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

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
###### Suggested Courses
Course of the Month11 days, 18 hours left to enroll