How to write/ combine two expressions in Derived column

Question

Post reply

How to write/ combine two expressions in Derived column

jyoti_bhatt

SSCommitted

Points: 1950
More actions
January 26, 2010 at 8:37 am

#156613

Hi,
I have two expessions that I want to combine to be used in one column in derived column. How can I do this?
Please help.
Many Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you probably want to show us the two expressions, and how you want them to be used together , i would think.

a crappy example is the ISNULL function is an expression. so something like

ISNULL(YourColumn,0.0) + ISNULL(YourColumn2,0.0) AS Total

is one example of using two expressions to make a signle derived value.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 2

Many Thanks for your response much appreciated.

These are my two expressions, how would I combine then?

REPLACE([Column_name],"-","")

(ISNULL([Column_name]) ? "" : [Column_name])

Lowell SSC Guru Points: 323504 More actions · Answer 3

jyoti_bhatt (1/26/2010)
Many Thanks for your response much appreciated.
These are my two expressions, how would I combine then?
REPLACE([Column_name],"-","")
(ISNULL([Column_name]) ? "" : [Column_name])

gotcha; you want to replace any value with isnull first, and then replace the dash; if the REPALCe function received Null it would crash with an error...so it would be like this:

REPLACE( (ISNULL([Column_name]) ? "" : [Column_name]) ,"-","")

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 4

Wowww. u solved my problem in no time.

Thank you so much for this much appreciated, it worked.

Many Thanks.

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 5

How can I amend this expression to replace it to NULL rather than "" (a space)?

REPLACE([Column_name],"-","")

All help will be appreciated?

Many Thanks.

Lowell SSC Guru Points: 323504 More actions · Answer 6

jyoti_bhatt (1/27/2010)
How can I amend this expression to replace it to NULL rather than "" (a space)?
REPLACE([Column_name],"-","")
All help will be appreciated?
Many Thanks.

jyoti you would not replace part of a string with NULL; that's not what it's for.

I don't think you have a complete handle on how the REPLACE function works; you'd use replace like you were doing previously, to remove unwanted parts of strings with an empty string or space;

why don't you give us a specific example; what you have now is finding a hyphen and replacing it with an empty string..i might do that to a phone number for example, where i want to store the numbers, but not the hyphen.

show us a specific example of what you are wanting to do.

in tsql, string + null is null, so you'd erase the value instead of doing whatever you were thinking.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 7

Thanks for your reply.

I've used the below expression and that removes hyphen with NULL.

[column name] == "-" ? NULL(DT_WSTR,4) : [column name]

That worked for me.

Thanks anyways.

mutiaarisandi Newbie Points: 7 More actions · Answer 8

mutiaarisandi

Newbie

Points: 7

July 22, 2018 at 10:47 pm

#1998547

okey thanks