Update numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update numbers

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2014-03-31 : 12:27:22

How can I update the following original column to look like the updated column? The column length is 15. I want to remove the dashes and add zeroes.

Original Updated
11111- 2631- 3 1111100263103
11111-304769- 2 1111130476902
11111- 25406- 3 1111102540603
22222- 14- 5 2222200001405
22222- 112-33 2222200011233

1st section has length of 5
2nd section has length of 6
and the last section has length of 2
when the column length is 15.

It should update to a column length of 13.

Thanks,
Marsha

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-31 : 13:24:26

[code]

SELECT
original,
expected_updated,
RIGHT(REPLICATE('0', 5) + SUBSTRING(original, 1, loc_dash1 - 1), 5) +
RIGHT(REPLICATE('0', 6) + LTRIM(SUBSTRING(original, loc_dash1 + 1, loc_dash2 - loc_dash1 - 1)), 6) +
RIGHT(REPLICATE('0', 2) + LTRIM(SUBSTRING(original, loc_dash2 + 1, 2)), 2) AS updated
FROM (
SELECT '11111- 2631- 3' as original, '1111100263103' AS expected_updated UNION ALL
SELECT '11111-304769- 2', '1111130476902' UNION ALL
SELECT '11111- 25406- 3', '1111102540603' UNION ALL
SELECT '22222- 14- 5', '2222200001405' UNION ALL
SELECT '22222- 112-33', '2222200011233'
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('-', original) AS loc_dash1
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', original, loc_dash1 + 1) AS loc_dash2
) AS ca2

[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 13:46:47

You could also use a split function, like:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

DECLARE @Foo TABLE(Val VARCHAR(15))

INSERT @Foo VALUES
('11111- 2631- 3')


SELECT *
FROM
	@Foo
CROSS APPLY
    (
        SELECT 
			CASE 
				WHEN B.Position = 1 THEN RIGHT(REPLICATE('0', 5) + RTRIM(LTRIM(b.Value)), 5)
				WHEN B.Position = 2 THEN RIGHT(REPLICATE('0', 6) + RTRIM(LTRIM(b.Value)), 6)
				WHEN B.Position = 3 THEN RIGHT(REPLICATE('0', 2) + RTRIM(LTRIM(b.Value)), 2)
			END
		FROM
			[dbo].[SplitString8KToVarchar](Val, '-') AS b
        ORDER BY
			b.Position   
        FOR XML PATH('')     
    ) AS b (Updated)

EDIT: cut-n-paste error

mlawton
Starting Member

35 Posts

Posted - 2014-03-31 : 14:42:55

Can this be made into a single line in a select statement? Case when original etc.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 14:57:08

Is it guaranteed that there are always the correct number of spaces between the dash(-) and the next value? If so, you could do a replace on space (' ') with zero ('0') and then replace the dashes with blank.

mlawton
Starting Member

35 Posts

Posted - 2014-03-31 : 15:05:07

That sounds good. Can you show me how?

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-31 : 15:26:38

Try this...


DECLARE @String VARCHAR(20) = '22222- 112-33'

SELECT @String AS [Original]
	, SUBSTRING(@String, 1, CHARINDEX('-', @String,0)-1) AS [First]
	, SUBSTRING(SUBSTRING(@String, CHARINDEX('-', @String, 0) + 1, LEN(@String)), 1, CHARINDEX('-', SUBSTRING(@String, CHARINDEX('-', @String, 0) + 1, LEN(@String)), 0)-1) AS [Second]
	, REVERSE(SUBSTRING(REVERSE(@String), 1, CHARINDEX('-', REVERSE(@String), 0)-1)) AS [Third]
	, RIGHT('00000'+LTRIM(RTRIM(SUBSTRING(@String, 1, CHARINDEX('-', @String,0)-1))),5)+
	  RIGHT('000000'+LTRIM(RTRIM(SUBSTRING(SUBSTRING(@String, CHARINDEX('-', @String, 0) + 1, LEN(@String)), 1, CHARINDEX('-', SUBSTRING(@String, CHARINDEX('-', @String, 0) + 1, LEN(@String)), 0)-1))),6)+
	  RIGHT('00'+LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(@String), 1, CHARINDEX('-', REVERSE(@String), 0)-1)))),2) AS [Final]

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 16:18:15

quote:
Originally posted by mlawton

That sounds good. Can you show me how?

DECLARE @Foo TABLE(Val VARCHAR(15))

INSERT @Foo VALUES
('11111-  2631- 3')


SELECT REPLACE(REPLACE(Val, ' ' , '0'), '-', '')
FROM @Foo

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-31 : 17:22:37

quote:
Originally posted by Lamprey

quote:
Originally posted by mlawton

That sounds good. Can you show me how?
DECLARE @Foo TABLE(Val VARCHAR(15))

INSERT @Foo VALUES
('11111- 2631- 3')

SELECT REPLACE(REPLACE(Val, ' ' , '0'), '-', '')
FROM @Foo

Lamprey,

if you see the sample data, I don't think thats they way it works for the OP.. He might have '-' and empty spaces or may not...

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 18:10:17

Yep, that's why I asked if the spaces are guaranteed or not.

mlawton
Starting Member

35 Posts

Posted - 2014-04-01 : 09:40:25

Thank you guys so much. This worked perfectly. SELECT REPLACE(REPLACE(Val, ' ' , '0'), '-', '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources