CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Darren
Darren

Reputation: 139

Removing text and adding to front of string

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.

Upvotes: 0

Views: 469

Answers (3)

Dibstar
Dibstar

Reputation: 2364

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

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

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

Upvotes: 2

Sparky
Sparky

Reputation: 15105

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)

Upvotes: 0

Related Questions