question

patdev-3689 avatar image
0 Votes"
patdev-3689 asked patdev-3689 commented

multiple column from one thorugh drived column

Hello,

I need help to convert column in to multiple column how can i use in derived column or something..
wanted to use following loging"
hightinfeet: cast(Ieft(heignt,1)*12.00) as decimal (7,3))
hightininch:
(
case isnumeric(replace(right(height, len(height)-2, ' " ', ''))
When 1 then convert (decimal(7,3),replace(right(height,len(height-2), ' " ', ' ' ))
else 0
end
)

eg: height

data
64
511
611
65
Thanks

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered patdev-3689 commented

Hi @patdev-3689 ,

We can use Derived Column and Conditional Split to get the desired output in ssis package.

Please refer to the following expressions and pictures:

88094-df.png

88006-derivedcolumn0.png
hightinfeet
(DT_NUMERIC,7,3)(((DT_I4)LEFT(height,1)) * 12.00)

CheckISNULL
LEN(REPLACE(RIGHT(height,LEN(height) - 2)," '' "," "))

88070-conditionalsplit.png

88111-derivedcolumn1.png
hightininch
(DT_NUMERIC,7,3)(REPLACE(RIGHT(height,LEN(height) - 2)," '' "," "))

88103-derivedcolumn2.png

88083-output.png

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




df.png (102.4 KiB)
derivedcolumn0.png (21.3 KiB)
derivedcolumn1.png (20.0 KiB)
derivedcolumn2.png (19.1 KiB)
output.png (24.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

just a question. the data is coming in as 64 or 5 11 then it would be 72 feet and 4 inches or 60 feet 11 inches.

please let me know.

0 Votes 0 ·

Hi @patdev-3689 ,

May I know from which unit do you want to convert to feet/inch?

Best Regards,
Mona

0 Votes 0 ·

The data has the format of following in the system:

5'6" (60 feet 6 inches)
6'10" (72 feet 10 inches)

as far as i know

Thanks

0 Votes 0 ·