Converting DataType=TEXT to DataType=STR

Hi,

I have an input text file which contains line(s) which can have more than 8000 characters.

I am able to read the lines in this file by specifying that the input column is data type = Text Stream [DT_TEXT] and I can then write the lines to another text file.

But, what I want to do is to use only the right-most 8000 characters. When I try to convert the input column to data type = String [DT_STR] using the Data Conversion transformation then I get the error:-

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I cannot use the string manipulation functions e.g. RIGHT or SUBSTRING in a Derived Column transformation because these functions do not work with DT_TEXT data.
Any Ideas?!

Thanks.You'd only need *1* line of custom code in a Script component transformation to extract your rightmost 8000 characters before moving on to the data type conversion.

-Doug|||Thanks Doug ... Did you mean a Script Component which looks something like:-

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ascii As Text.Encoding

Dim asciiChars(10) As Char

ascii.GetChars(Row.InputColumn.GetBlobData(1, 1), 0, 1, asciiChars, 0)

Dim asciiString As New String(asciiChars)

MsgBox(asciiString)

End Sub

End Class

When I run the package it fails at the ascii.getchars line with the following error message:-

Error: 0xC0047062 at Data Flow Task, Script Component [106]: System.NullReferenceException: Object reference not set to an instance of an object.

All I am trying to do is extract the first byte and convert it to an ascii char (before I go on to work out the lengths to extract the right-most 8000 chars etc.)

Is this what you had in mind? Or, is there another way to do it?

Many Thanks.

|||This should do what you want. You will need to import System.Text.Encoding.

Dim Start As Integer = Max(0, CInt(Row.InputColumn.Length) - 8001)
Dim Length As Integer = Min(8000, CInt(Row.InputColumn.Length))
Dim asciiString As String = ASCII.GetString(Row.InputColumn.GetBlobData(Start, Length)

|||Just to confirm that Jay's suggestion works. Many Thanks.sqlsql

Convert Varchar is inconsistent

Hi All,
I have a source table which contains a customer code in a CHAR(8) column.
The customer codes are only ever 5 or 6 characters long, so in the reporting
tables I am extracting data out to, I have been specifying the customer code
column as a VARCHAR(8). [I am aware that for such a small variance its
debatable whether its worth the overhead of having a VARCHAR column, but jus
t
run with me on this one].
In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
CustomerCode) but some of my tables still contain a padded 8 character
customer code, despite the datatype being VARCHAR(8) and the sp explicitly
stating convert this code to a varchar. I can verify this with the LEN and
DATALENGTH commands. All the tables are in the same database and I havent
been playing with the SET ANSI_PADDING setting either.
I'm thinking that SQL is evaluating whether its worth doing the conversion,
and sometimes it thinks its worthwhile, and other times it doesnt (maybe on
whether it comes accross a 5 or a 6 character customer first?). However, I
cant find anything about this 'machine learning' feature in BOL.
Has anyone come accross this before? and if so, what did you do to force SQL
into storing the data in the column as a VARCHAR?
TIA,
Bill PColumns of type CHAR are padded with space (See ANSI_PADDING in BOL), so whe
n
you convert to varchar you are converting also the spaces and they are not
trimmed in the convertion. You need to rtrim the converted value.
Example:
create table t (
colA char(8) not null
)
insert into t values('a')
insert into t values('bb')
insert into t values('ccc')
insert into t values('dddd')
insert into t values('eeeee')
insert into t values('gggggg')
insert into t values('hhhhhhhh')
select
convert(varchar(8), colA),
datalength(convert(varchar(8), colA)),
rtrim(convert(varchar(8), colA)),
datalength(rtrim(convert(varchar(8), colA)))
from
t
select
convert(varchar(1), space(1)),
datalength(convert(varchar(1), space(1))),
len(convert(varchar(1), space(1)))
drop table t
go
AMB
"Bill P" wrote:

> Hi All,
> I have a source table which contains a customer code in a CHAR(8) column.
> The customer codes are only ever 5 or 6 characters long, so in the reporti
ng
> tables I am extracting data out to, I have been specifying the customer co
de
> column as a VARCHAR(8). [I am aware that for such a small variance its
> debatable whether its worth the overhead of having a VARCHAR column, but j
ust
> run with me on this one].
> In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
> CustomerCode) but some of my tables still contain a padded 8 character
> customer code, despite the datatype being VARCHAR(8) and the sp explicitly
> stating convert this code to a varchar. I can verify this with the LEN an
d
> DATALENGTH commands. All the tables are in the same database and I havent
> been playing with the SET ANSI_PADDING setting either.
> I'm thinking that SQL is evaluating whether its worth doing the conversion
,
> and sometimes it thinks its worthwhile, and other times it doesnt (maybe o
n
> whether it comes accross a 5 or a 6 character customer first?). However,
I
> cant find anything about this 'machine learning' feature in BOL.
> Has anyone come accross this before? and if so, what did you do to force S
QL
> into storing the data in the column as a VARCHAR?
> TIA,
> Bill P|||On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
(snip)
> [I am aware that for such a small variance its
>debatable whether its worth the overhead of having a VARCHAR column, but ju
st
>run with me on this one].
Hi Bill,
It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
characters.
But okay - I'll run with you.

>In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
>CustomerCode) but some of my tables still contain a padded 8 character
>customer code, despite the datatype being VARCHAR(8) and the sp explicitly
>stating convert this code to a varchar.
That's correct. As CHAR(8), the data got padded with spaces. The
conversion to VARCHAR won't remove the trailing spaces.
Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
spaces and really reduce the length.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Alejandro,
Many thanks for that. Thats something that I hadnt considered, but it makes
total sense. I will ensure that I perform RTRIMs in all the SPs to ensure
consistency.
I doesnt answer why some of my tables were RTRIMing themselves and some
werent though? Thats not something I want you to answer as I am more than
happy with the solution, but its still something that I dont at this point
fully understand. If I find out, I will post it to the group.
Thanks once again,
Bill P
"Alejandro Mesa" wrote:
> Columns of type CHAR are padded with space (See ANSI_PADDING in BOL), so w
hen
> you convert to varchar you are converting also the spaces and they are not
> trimmed in the convertion. You need to rtrim the converted value.
> Example:
> create table t (
> colA char(8) not null
> )
> insert into t values('a')
> insert into t values('bb')
> insert into t values('ccc')
> insert into t values('dddd')
> insert into t values('eeeee')
> insert into t values('gggggg')
> insert into t values('hhhhhhhh')
> select
> convert(varchar(8), colA),
> datalength(convert(varchar(8), colA)),
> rtrim(convert(varchar(8), colA)),
> datalength(rtrim(convert(varchar(8), colA)))
> from
> t
> select
> convert(varchar(1), space(1)),
> datalength(convert(varchar(1), space(1))),
> len(convert(varchar(1), space(1)))
> drop table t
> go
>
> AMB
>
> "Bill P" wrote:
>|||Hi Hugo,
If only life were that simple. If I set these up as CHAR(6) or VARCHAR(6)
you can bet your bottom dollar that the some bright spark will create a new
customer with an 8 character code, simply because the ERP system lets them.
But I take your point.
Bill :-)
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
> (snip)
> Hi Bill,
> It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
> bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
> characters.
> But okay - I'll run with you.
>
> That's correct. As CHAR(8), the data got padded with spaces. The
> conversion to VARCHAR won't remove the trailing spaces.
> Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
> spaces and really reduce the length.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 07:07:04 -0800, Bill P wrote:
(snip)
> the ERP system lets them.
Ah, I see how that changes things. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx
.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert text to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?
If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
I believe Enterprise Manager will recreate the table to implement this
change so the original text pages are deleted. You can verify this by
clicking on the 'Save change script' button after making the change in the
design table GUI.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Morris" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4ba01c41d81$d9afa490$a101280a@.phx.gbl...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
|||Dan,
Thanks much.
I have never noticed that the "change script" icon appears
when I click save.

Convert number into Chinese Text

Please help to advoce how to convert a number into text (Chinese characters). For example, to display $2880.85 into:
貳仟捌佰捌拾元捌拾伍仙
Thankshow about making out a formular like

if = 2 then...?

other wise is quite hard... due to chinese have to enter the 佰捌, 元 and 仙...
thats difficult|||I can create a mapping but need to identify the correct location of the digit first. In this example, how do I separate the 2 from the entire figure so that it is equal to 2000 first, and the 1st 8 is 800, and so forth for the rest of the digits? What is the formula to do this identification?

If I can tick out the correct place of the digits, then I can map it to the correct translation, say if X's dec. place is in thousand, then translate 2 to Chinese text + the chinese thousand sign...

Pls help to define the formula to tick out the digits' dec. place...Thanks

Convert Money to Char

I have a sql query listed below, I would like to count the number of values
that the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000You are very close. You're mistake is mostly with the "like."
Select count(iNum)
from Call_Movement
where left(cast(iNum as varchar(30)),5) = '80348'
I do not know your data as well as you, but I can see a day when there
will be more than twenty characters available and this code failing.
Joe K. wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000|||Dear joe,
instead of cast and substring, u can use convert()
e.g.,
SELECT count(iNum) from Call_Movement
where convert(varchar(50), iNum,0) like '80438%'
Thanks & Regards
Ravi
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>
>

Convert Field From VarChar To Int With Speical Characters In Field

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

If you put some code logic like what I have below into a user-defined function, it should remove anything at the end of your number value that you want to eliminate. Then you can use an update statement and use the UDF for the new value. This will only work if the extraneous text is at the end of the values though.

declare @.a nvarchar(100)

set @.a = '15483ktr'

declare @.numpart nchar(1)

declare @.x int

set @.x = 1

set @.numpart = SUBSTRING(@.a,@.x,1)

While IsNumeric(@.numpart) = 1

BEGIN

set @.x = @.x + 1

set @.numpart = SUBSTRING(@.a,@.x,1)

END

print SUBSTRING(@.a, 1, @.x-1)

|||

This trims all characters LEFT of the first Numeral, and all characters RIGHT of the first NON-numeral:

ABC123 becomes 123

123ABC becomes 123

ABC1234DEF becomes 1234

ABC12.34 becomes 12

1234ABC456 becomes 1234

ABC1234DEF5678 becomes 1234

For this reason, I've remarked the Update statment, so you can run this on your table and see what turns into what

Code Snippet

--Update MyTable set <MyField> =

select <MyField>,

left(substring(<MyField>, patindex('%[0-9]%',<MyField>),8000) + 'A',

patindex('%[^0-9]%',substring(<MyField>, patindex('%[0-9]%',<MyField>),8000) + 'A')-1)

from <MyTable>

where <MyField> like '%[0-9]%'

Convert DBs numeric date to SQL datetime

Hi,
I'm moving some data from AS400 DB2 to SQL Server and run
into date conversion problems.
My date from DB2 is numeric 8 characters eg "20031231".
How can I convert it to either datetime or smalldatetime
format in SQL server ?
If my table in SQL is defined as datetime/smalldatetime or
numeric, I will get error during the data pump process.
If I define in char format, data pump works fine but data
isn't the format I want ?
Can someone help ? Thanks.If the date is part of the PK for a fact or dim table, I would use an intege
r key and not datetime. INT or DECIMAL(9,0) should be sufficient for your ne
eds if that is the case. I sometimes import .txt files from AS/400 and the s
ource table must not allow
packed signs. In the activex script transforming the data from source to des
tination you could convert int to datetime or char to int to datetime if you
wish.
You might want to consider importing textfiles and not use the source table
directly since this option gives you a snapshot of the data. If the import f
ails you will always have a source file to check for errors. A transactional
source table might change
and the error be corrected without your knowledge.