Removing text and adding to front of string

Question

How would I go about removing a string from a field in a sql query (using sql server 2005) and adding it to the front of the string?

For instance my search string field contains: 22378MA

I want to search for the following characters 'MA' in this case.

I would like the query to add this string to the front so that it returns a query like this:

MA2237

My field name is sku for the query.

Not sure I explained myself properly. I don't want to change the field only what is returned in the query in a view. In addition the field value changes so I can't hardcode the sku. In addition the sku length field length is variable. The suffix 'MA' may be changed for certain queries so I need to be able to use it in a case statement.

How does one know what part of MA2237 should be searched at the end of the column value? What says that it should be MA and not MA2 or M? What is the defining rule? — Andriy M
– Andriy M, Commented Oct 21, 2011 at 12:55

D'Arcy Rittich · Accepted Answer · 2011-10-21 02:39:22Z

2

select SKU as OldSKU, case 
    when CHARINDEX('MA', SKU) = LEN(SKU) - 1 
        then 'MA' + SUBSTRING(SKU, 1, LEN(SKU) - 2) 
    when CHARINDEX('B', SKU) = LEN(SKU)
        then 'B' + SUBSTRING(SKU, 1, LEN(SKU) - 1) 
    when CHARINDEX('XYZ', SKU) = LEN(SKU) - 2 
        then 'XYZ' + SUBSTRING(SKU, 1, LEN(SKU) - 3) 
    else SKU
    end as NewSKU
from (
    select '22378MA' as SKU
    union all
    select '22378B'
    union all
    select '22378XYZ'
    union all
    select '22378TT'
) a

Output:

OldSKU   NewSKU
-------- -----------
22378MA  MA22378
22378B   B22378
22378XYZ XYZ22378
22378TT  22378TT

edited Oct 21, 2011 at 2:39

answered Oct 20, 2011 at 21:25

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darren Over a year ago

Not sure I explained myself properly. I don't want to change the field only what is returned in the query in a view. In addition the field value changes so I can't hardcode the sku. In addition the sku length field length is variable. The suffix 'MA' may be changed for certain queries so I need to be able to use it in a case statement.

Darren Over a year ago

Thanks Redfilter, but how do I look specifically for 'MA' should I use a case statement to check for it.

Dibstar · Accepted Answer · 2011-10-21 10:21:49Z

1

Alternatively if your suffix is always a text string after numerics, you could use:

;with data as
(
SELECT '22378MA' as sku UNION ALL
SELECT '22444378B' as sku UNION ALL
SELECT '12345GHJ' as sku UNION ALL
SELECT '78456M' as sku
)
SELECT 
sku
,RIGHT(sku,LEN(sku) - PATINDEX('%[A-Za-z]%',sku) + 1) + '' + LEFT(sku,PATINDEX('%[A-Za-z]%',sku) - 1) as sku2 
from data

Which will put the text (however long it is) before the numbers in the string

answered Oct 21, 2011 at 10:21

Dibstar

2,3742 gold badges25 silver badges38 bronze badges

Comments

Sparky · Accepted Answer · 2011-10-20 21:26:18Z

0

Try this to see if you get the results you want

select sku,substring(sku,6,7)+substring(sku,1,5)
from table

If it works OK

  update table set sku = substring(sku,6,7)+substring(sku,1,5)

answered Oct 20, 2011 at 21:26

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Collectives™ on Stack Overflow

Removing text and adding to front of string

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related