MS SQL 2005 substring - replace question

Posted on 2012-09-18
Last Modified: 2012-09-18
I have a field in a table that contains data that I need to manipulate for sorting.  And I need to do this just as an output  using a select query (I don't want to change it in the table).  I need to select 3 fields; tempid, type, location.    The "location" field contains the data to be manipulated.   Here's a few examples:

SB-01-F3-015 -  I need this to OUTPUT as SB-01-3F-015
SB-01-B4-010 -  I need this to OUTPUT as SB-01-4B-010
SB-01-D2-008 -  I need this to OUTPUT as SB-01-2D-008

I assume you use some kind of replace command...  The command will always be transposing the 4th and 5th character of the location field.
Question by:RavenTim
    LVL 17

    Accepted Solution

    SELECT LEFT(Location,6) + REVERSE(SUBSTRING(Location,7,2)) + RIGHT(Location,4)
    FROM yourtable
    LVL 13

    Expert Comment

    Assuming your column name is myCol, this could work.

    SELECT LEFT(myCol,6)+REVERSE(SUBSTRING(myCol,7,2))+RIGHT(myCol,4)

    Author Closing Comment


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    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…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now