CODEWITHSUNDEEP
sqlsql-server-2012
whitz11
whitz11

Reputation: 229

Concatenating various characters

I have a series of ID numbers like this

    ABC/12345/2012
    DEF/67891/2013
    GHI/23456/2014
    KLM/78911/2014

I need to change them so they look like this

    12-12345
    13-67891
    14-23456
    14-78911
    14-6634

The below works to a degree but I have a few that only have 4 numbers in, they should be proceeded by a zero. 

   SELECT RIGHT(ID, 2)+'-'+RIGHT(SUBSTRING(ID, CHARINDEX('/', ID, 1)-1, LEN(ID)-7), 5)

    12-12345
    13-67891
    14-23456
    14-78911
    14-/6634

So I need 14-/6634 to look like 14-06634

Upvotes: 2

Views: 50

Answers (3)

B3S
B3S

Reputation: 1051

Assuming your column name is ID, and length of each substrings between the '/' characters is not variable (ABC = 3, 12345 = 5, 2012 = 4): 

SELECT RIGHT(ID, 2)+'-'+RIGHT(SUBSTRING(ID, CHARINDEX('/', ID, 1)-1, LEN(ID)-7), 5)

Based on your main post edit:

SELECT RIGHT(ID, 2)+'-'+REPLACE(RIGHT(SUBSTRING(ID, CHARINDEX('/', ID, 1)-1, LEN(ID)-7), 5), '/', '0')

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

If your data is fixed format, then you can use PARSENAME

Sample execution with sample data:

DECLARE @TestTable TABLE (TestData VARCHAR (50));

INSERT INTO @TestTable (TestData)
SELECT 'ABC/12345/2012' UNION
SELECT 'DEF/67891/2013' UNION
SELECT 'GHI/23456/2014' UNION
SELECT 'KLM/78911/2014'

SELECT RIGHT(PARSENAME(REPLACE(TestData, '/', '.'), 1), 2) + '-' +
       PARSENAME(REPLACE(TestData, '/', '.'), 2) AS TestData
FROM @TestTable

Result:

TestData
--------
12-12345
13-67891
14-23456
14-78911

Upvotes: 0

pm_li
pm_li

Reputation: 43

Try this

declare @tmp varchar(50) = 'ABC/12345/2012'

select SUBSTRING(@tmp, len(@tmp) - 1, 2) + '-' + SUBSTRING(@tmp,CHARINDEX('/',@tmp)+1,LEN(@tmp))

it gives you 

12-12345/2012

and now you have to remove /2012

Upvotes: 0

Related Questions