Cast-Convert question

Question

Post reply

Cast-Convert question

walter grimes

SSC Rookie

Points: 49
More actions
October 12, 2005 at 3:15 am

#166458

I have a decimal(8,0) field that I need to break into 2 parts, for example 10005555 will need to be 1000 decimal(4,0) and in another field 5555 decimal(4,0). I can use the cast and substring function but how do I get this back to decimal data Type? Thanks in advance
Walter

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DECLARE @Value DECIMAL(8,0)

SELECT @Value = 10005555

SELECT CAST(SUBSTRING(CAST (@Value AS VARCHAR(8)),1,4) AS DECIMAL(4,0)) AS FirstHalf,

CAST(SUBSTRING(CAST (@Value AS VARCHAR(8)),5,4) AS DECIMAL(4,0)) AS SecondHalf

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

walter grimes SSC Rookie Points: 49 More actions · Answer 2

Thanks so much for the help that worked for me

Walter

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

How bout something like

DECLARE @val DECIMAL(8, 0)

DECLARE @Table TABLE

(FirstHalf DECIMAL(4, 0),

SecondHalf DECIMAL(4, 0))

SET NOCOUNT ON

SET @val = 10005555

INSERT INTO @Table (FirstHalf, SecondHalf)

SELECT LEFT(@Val, 4) FirstHalf, RIGHT(@Val, 4) SecondHalf

SELECT FirstHalf, SecondHalf,

CAST(CAST(FirstHalf AS CHAR(4)) + CAST(SecondHalf AS CHAR(4)) AS DECIMAL(8, 0)) PutBackTogether

FROM @Table

SET NOCOUNT OFF

Good Hunting!

AJ Ahrens

webmaster@kritter.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

Why do all the conversions:

DECLARE @val decimal(8,0) DECLARE @val1 decimal(4,0) DECLARE @val2 decimal(4,0) set @val=10005555 set @val1 = floor(@val/10000) set @val2 = @val-(@val1*10000)
print 'V1:'+cast(@val1 as char(4)) print 'V2:'+cast(@val2 as char(4))
V1:1000 V2:5555

vadba SSChampion Points: 11133 More actions · Answer 5

Walter, are all the values more than 4 digits? If not, then you will get an error with the conversion. To be safe, you could use one of the following instead:

SELECT CONVERT(decimal(4,0), Left(Right('00000000' + CONVERT(varchar(8), @Value), 8), 4)) AS FirstHalf

, CONVERT(decimal(4,0), Right(Right('00000000' + CONVERT(varchar(8), @Value), 8), 4)) AS SecondHalf

or

SELECT CONVERT(decimal(4,0), CONVERT(int, @value) / 10000) AS FirstHalf

, CONVERT(decimal(4,0), CONVERT(int, @value) % 10000) AS SecondHalf

walter grimes SSC Rookie Points: 49 More actions · Answer 6

Yes the orginal value is 8,I will try that way as well. The code that Gila put here works as well. So I am going to try to put this all together in a view. The end result should be a daily revenue report by department. The first 4 digits are the dept and the last 4 is the type of revenue. Y'all have really helped me on this, many thanks

Walter