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.
Author |
Topic |
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-21 : 06:58:11
|
You can use the Derived Column to do this. N 56°04'39.26"E 12°55'05.63" |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2012-02-21 : 07:07:57
|
Thanks peso I'll take a look-----------What color do you want that database? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-21 : 07:56:03
|
The "Derived Column" is exactly how it works in a T-SQL query., CAST(Col1 AS INT) AS newColumn, LTRIM(RTRIM(Col2)) AS newColumn2 N 56°04'39.26"E 12°55'05.63" |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2012-02-21 : 08:32:34
|
I took a look and it does exactly what I want, in fact it does it better than I hoped. I'm now trying to work out how to check if a string contains a numeric value and set it to null if it doesn't. With SQL I did something like UPDATE MyTableSET MyField = NULLWHERE MyField like '%[^0-9]%'steve-----------What color do you want that database? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 14:33:31
|
quote: Originally posted by elwoos I took a look and it does exactly what I want, in fact it does it better than I hoped. I'm now trying to work out how to check if a string contains a numeric value and set it to null if it doesn't. With SQL I did something like UPDATE MyTableSET MyField = NULLWHERE MyField like '%[^0-9]%'steve-----------What color do you want that database?
you can achieve it using expression inside derived column using conditional operator.for filter part use FINDSTRING to find location------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2012-02-22 : 04:11:01
|
Thanks Visakh.I've decided to take this sort out stuff out of the SSIS and put it in a stored procedure and just use SSIS for importing data and to then run the sproc.steve-----------What color do you want that database? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-22 : 04:41:44
|
That's the way we handle our files.SSIS is optimized for copying the data from the files to the staging table (Extraction), and then we run a procedure to do all the Transformation and Loading. N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|