[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Returning column data in a different order in sql server 2005

I have the following table in a sql server 2005 database
TABLE
COLUMN A                             COLUMN B
Acme.jones.net      Sales/Region/South

   I want a query to return the following
Acme.jones.net/South/Region/Sales

SELECT [COLUMN A] +’/’+ [COLUMN B] FROM TABLE
Gives me
Acme.jones.net/ Sales/Region/South

Any ideas on how I can “reverse” the values in COLUMN  B before returning?
thanks

0
blossompark
Asked:
blossompark
  • 6
  • 3
1 Solution
 
sdstuberCommented:
use substring to extract the pieces and rearrange them,  maybe something like this...


SELECT [COLUMN A] +'/'+ substring([COLUMN B],charindex([COLUMN B],'/',charindex([COLUMN B],'/')+1),999) + substring([COLUMN B],charindex([COLUMN B],'/') , charindex([COLUMN B],'/',charindex([COLUMN B],'/')) - charindex([COLUMN B],'/')) + substring([COLUMN B],1,charindex([COLUMN B],'/')-1)
 FROM TABLE
0
 
sdstuberCommented:
oops, sorry about that,  lots of () and , and reversed parameters above

SELECT charindex('/',[COLUMN B]),[COLUMN A]
  + substring([COLUMN B],charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1),99)
  + substring([COLUMN B],
                  charindex('/',[COLUMN B]) ,
                  charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1) - charindex('/',[COLUMN B])+1)
   + substring([COLUMN B],1,charindex('/',[COLUMN B])-1)
 FROM yourtable
0
 
blossomparkAuthor Commented:
Hi sdstuber, thanks for that,
  have run the code an d am getting the following error;
Invalid length parameter passed to the SUBSTRING function.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
which one?  The second one worked when I tested it.  I used the exact data you have posted in your question
0
 
sdstuberCommented:
Here's the test case I used


with yourtable as (
select 'Acme.jones.net' [COLUMN A],'Sales/Region/South' [COLUMN B]
)
SELECT [COLUMN A]
  + substring([COLUMN B],charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1),99)
  + substring([COLUMN B],
                  charindex('/',[COLUMN B]) ,
                  charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1) - charindex('/',[COLUMN B])+1)
   + substring([COLUMN B],1,charindex('/',[COLUMN B])-1)
 FROM yourtable
0
 
blossomparkAuthor Commented:
My apologies,
I gave you 'Sales/Region/South'
which should have been
'Sales/Region/South/Western

sorry again'
0
 
sdstuberCommented:
you do the same thing as above, just keep extending it


SELECT [COLUMN A]
  + substring([COLUMN B],
              charindex('/',[COLUMN B],charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1)+1),
              99)
  + substring([COLUMN B],
                 charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1),
                 charindex('/',[COLUMN B],charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1)+1)
                  - charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1) )
  + substring([COLUMN B],
                  charindex('/',[COLUMN B]) ,
                  charindex('/',[COLUMN B],charindex('/',[COLUMN B])+1) - charindex('/',[COLUMN B])+1)
   + substring([COLUMN B],1,charindex('/',[COLUMN B])-1)
 FROM yourtable
0
 
sdstuberCommented:
if you need it to be more dynamic and support longer strings then I suggest writing your own function to parse the field
0
 
blossomparkAuthor Commented:
Hi sdstuber,
thanks for all your help,,,,your code works fine,,,,the error on my side...thanks again
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now