| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2007-12-04 : 11:02:39
|
| Hi i'd like to create a expression to find if the first character is 0 and if it is then don't display it, but if the first charactar is not 0 then its ok, am trying to do this within a drived column in SSIS |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-04 : 11:17:57
|
SELECT *FROM Table1WHERE Col1 NOT LIKE '0%'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 11:24:09
|
| How can i write this as a express in SSIS Derived column, i wan to replace the column if it has a 0 at the first char example014789632 would become 14789632 96325877 would be the same 96325877 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-04 : 11:35:24
|
If they are all numeric, CAST AS BIGINT.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 11:42:47
|
| Yes they are all numeric So would i get the results below014789632 would become 14789632 012345666 would be come 1234566696325877 would be the same 96325877 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-04 : 11:43:43
|
Yes.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 11:48:21
|
| ok am looking at the type cast in the drived column editor in SSIS but i can't see a bigint value they are like DT_I4 etc... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-04 : 11:50:58
|
If the column is only 8 digits maximum, DT_I4 will work just fine...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 12:00:24
|
| the column is 14 characters long.. i'll test this now and hope it will return all my characters. |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 12:08:20
|
| I tried that but am getting a error on it. my expression looks like this (DT_I8)colname |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-04 : 12:28:04
|
See, you found the BIGINT!
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-04 : 12:34:00
|
| But it does not give me the result i require, of where if there is a 0 at the start of the string it should be removed.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-05 : 03:25:11
|
| 1 Select cast(col as bigint) from yourtable2 Select case when col like '0%' then substring(col,1,len(col)) else col end from yourtableMadhivananFailing to plan is Planning to fail |
|
|
|
tm
Posting Yak Master
160 Posts |
Posted - 2007-12-05 : 10:01:58
|
| Hi All,I was just wondering if rookie_sql has a "Derived Column" Data Flow object in his/her SSIS package and would like to use the object to derive a new column.If this is the case .. in "Derived Column Transformation Editor" put "(DT_UI8)subcat" into Expressions column as this will cast string to Eight byte unsigned and select "eight-byte unsigned integer[DT_UI8]" for the data type. |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-06 : 06:58:53
|
| Hi thanks for your help with this. I made up the following experssion and it does that i require, i had to check was the first char of the string a 0 in some cases it was not a 0 so i did not want to remove it.FINDSTRING(col name,"0",1) == 1 ? RIGHT(col name,LEN(col name) - 1) : col name |
|
|
|
|
expression to find 0