How to transform text in number?

How to transform text in numberHello Slim !
Try this:
Select Convert(INT, @.Textstring)
Jens Süßmeyer.|||Explaining better:
to convert ' 2.00 ' for 2,00
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:ON2kpboZDHA.1280@.tk2msftngp13.phx.gbl...
> Hello Slim !
> Try this:
> Select Convert(INT, @.Textstring)
> Jens S meyer.
>|||Hello Slim !
Here some coding example: Try this:
DECLARE @.INTSTRING varchar(100)
SET @.INTSTRING = '2,001'
Select Replace(@.INTString,',','.')
Implicit convertion will be used, if you try to calculate with this value,
explicit convertion could be used by you:
Select
Convert(ANY_NUMERIC_DATATYPE_CHOOSEN_BY_YOU,Replace(@.INTString,',','.'))
Jens Süßmeyer.|||I am trying to matter and I am receiving that message
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:e3hHKroZDHA.1744@.TK2MSFTNGP12.phx.gbl...
> Hello Slim !
> Here some coding example: Try this:
> DECLARE @.INTSTRING varchar(100)
> SET @.INTSTRING = '2,001'
> Select Replace(@.INTString,',','.')
> Implicit convertion will be used, if you try to calculate with this value,
> explicit convertion could be used by you:
> Select
> Convert(ANY_NUMERIC_DATATYPE_CHOOSEN_BY_YOU,Replace(@.INTString,',','.'))
> Jens S meyer.
>|||I am using SQL in Brazil, as I do to substitute (.) point for (,) comma
DECLARE @.INTSTRING char(100)
SET @.INTSTRING = '0.50'
--Select Replace(@.INTString,'.',',')
Select convert(decimal(9,2),@.INTSTRING)
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:u5AJAIpZDHA.2136@.TK2MSFTNGP10.phx.gbl...
> OK, so then you need the other way, i am using a GERMAN SQL Server with ,
> separated numerics, so you have to replace the . with the , and vice versa
> in the replace function.
> Jens S meyer.
> "Slim" <fslim@.bol.com.br> schrieb im Newsbeitrag
> news:eOoHHEpZDHA.3768@.tk2msftngp13.phx.gbl...
> > Trying to convert received the message:
> >
> > DECLARE @.INTSTRING char(100)
> > SET @.INTSTRING = '2,001'
> > --Select Replace(@.INTString,'.',',')
> >
> > Select convert(decimal(10,5),@.INTSTRING)
> >
> > Server: Msg 8114, Level 16, State 5, Line 4
> > Error converting data type varchar to numeric.
> >
> >
> > "Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> > news:uPqtS$oZDHA.3436@.tk2msftngp13.phx.gbl...
> > > Try to transform it the way you will need it in the database (with
> > convert).
> > > If the target database needs decimal(10,5), convert it to
> > > convert(decimal(10,5),Column).
> > >
> > > Gru, Jens.
> > >
> > >
> > >
> >
> >
>sql

How To Text Search

I purchased a VB SQL 2005 book but it had no info on text searching ( a rewrite of the title of an old book to increase sales). My database is already up and running and I have several text fields of data.

Questions:

Do you need Full-Text searching to search within a text field or does SQL Exp offers basic search?

Can someone post example SELECT statments to search fields for text?

I'm interested in getting exact match and relevance.

Thank you so much.

Refer to Books Online about using the [ LIKE ] keyword, and using WILDCARDs.

For example, to find any row with an occurance of the characters 'FUL' at the beginning of the column:

WHERE MyColumn LIKE 'FUL%'

To find any row with an occurance of the characters 'FUL' anywhere in the column:

WHERE Mycolumn LIKE '%FUL%'

Books Online has many good examples.

In case you don't have a copy of Books Online, you can get your very own copy here.

How to take big raw text in SP as a paremeter!...............

Hi all,
I have raw text like these:
'test1111111111111111111111111111111111
test2222222222222222222222222222222222
test3333333333333333333333333333333333
test4444444444444444444444444444444444
test5555555555555555555555555555555555'
I want to create a SP that take these raw text as a paremeter and insert it
into a temp table as a different row. In this case, my temp table should
store:
test1...
test2...
test3...
test4...
I created a SP for this and defined a paremeter as text. However, for some
reason, the 'text' paremeter ONLY took limited text and truncated whatever
after that.
Thanks in advance!
Tom dHow did you pass the value to the parameter and from where?
If you are testing your sp in QA, and you pass the text as a value (a string
between apostrophes), then sql server consider it as varchar and the max
length for varchar is 8000.
AMB
"tom d" wrote:

> Hi all,
> I have raw text like these:
> 'test1111111111111111111111111111111111
> test2222222222222222222222222222222222
> test3333333333333333333333333333333333
> test4444444444444444444444444444444444
> test5555555555555555555555555555555555'
> I want to create a SP that take these raw text as a paremeter and insert i
t
> into a temp table as a different row. In this case, my temp table should
> store:
> test1...
> test2...
> test3...
> test4...
> I created a SP for this and defined a paremeter as text. However, for some
> reason, the 'text' paremeter ONLY took limited text and truncated whatever
> after that.
> Thanks in advance!
> Tom d
>|||You have to be wary about the functions you use to parse the string. Most
functions like CharIndex only work on VarChar data types and not on Text dat
a
types. You need to process the string in 8000 character chunks using somethi
ng
like Substring (which does work on Text data types) and split the values bas
ed
on the delimiter (in this case) of a line feed or carriage return and line f
eed.
Thomas
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:FD307709-71D4-42F5-9A29-CC94003922A3@.microsoft.com...
> Hi all,
> I have raw text like these:
> 'test1111111111111111111111111111111111
> test2222222222222222222222222222222222
> test3333333333333333333333333333333333
> test4444444444444444444444444444444444
> test5555555555555555555555555555555555'
> I want to create a SP that take these raw text as a paremeter and insert i
t
> into a temp table as a different row. In this case, my temp table should
> store:
> test1...
> test2...
> test3...
> test4...
> I created a SP for this and defined a paremeter as text. However, for some
> reason, the 'text' paremeter ONLY took limited text and truncated whatever
> after that.
> Thanks in advance!
> Tom d
>

How to switch off the toolbar params

This is a multi-part message in MIME format.
--=_NextPart_000_001C_01C56790.2E2B5330
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a report that is launched from a windows app using URL access. Is = it possible to turn off the parameters section of the toolbar without = turning off the other options. I have tried using &rc:Toolbar=3DFalse = but tis turns off the entire toolbar and not just the paramater input = boxes. Any ideas?
--=_NextPart_000_001C_01C56790.2E2B5330
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have a report that is launched from a = windows app using URL access. Is it possible to turn off the parameters section of = the toolbar without turning off the other options. I have tried using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not = just the paramater input boxes. Any ideas?
--=_NextPart_000_001C_01C56790.2E2B5330--This is a multi-part message in MIME format.
--=_NextPart_000_00BD_01C567BD.51B651B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im =Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from a windows app using URL access. =Is it possible to turn off the parameters section of the toolbar without =turning off the other options. I have tried using &rc:Toolbar=3DFalse =but tis turns off the entire toolbar and not just the paramater input =boxes. Any ideas?
--=_NextPart_000_00BD_01C567BD.51B651B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

What about rc:Parameters=3Dfalse =?
-- HTH, Jens Suessmeyer.
--http://www.sqlserver2005.de">http://www.sqlserver2005.de-=--
"Aidan Garnish" schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492=@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from =a windows app using URL access. Is it possible to turn off the parameters =section of the toolbar without turning off the other options. I have tried using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not =just the paramater input boxes. Any =ideas?

---

--=_NextPart_000_00BD_01C567BD.51B651B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0042_01C5681F.AD1CFC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perfect...thank you
"Jens S=FC=DFmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> =wrote in message news:ucBZOy6ZFHA.3976@.TK2MSFTNGP15.phx.gbl...
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im =Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched from a windows app using URL =access. Is it possible to turn off the parameters section of the toolbar =without turning off the other options. I have tried using =&rc:Toolbar=3DFalse but tis turns off the entire toolbar and not just =the paramater input boxes. Any ideas?
--=_NextPart_000_0042_01C5681F.AD1CFC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Perfect...thank you
"Jens S=FC=DFmeyer" wrote in message news:ucBZOy6ZFHA.3976=@.TK2MSFTNGP15.phx.gbl...
What about rc:Parameters=3Dfalse ?
-- HTH, Jens Suessmeyer.

--http://www.sqlserver2005.de">http://www.sqlserver2005.de-=--
"Aidan Garnish" schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492=@.TK2MSFTNGP14.phx.gbl...
I have a report that is launched =from a windows app using URL access. Is it possible to turn off the parameters =section of the toolbar without turning off the other options. I have tried =using &rc:Toolbar=3DFalse but tis turns off the entire toolbar and not =just the paramater input boxes. Any ideas?

---

--=_NextPart_000_0042_01C5681F.AD1CFC40--|||rc:parameters=collapsed can work,too
(sp1 later version)
"Aidan Garnish" wrote:
> Perfect...thank you
> "Jens Sü�meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message news:ucBZOy6ZFHA.3976@.TK2MSFTNGP15.phx.gbl...
> What about rc:Parameters=false ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag news:uvrO5e4ZFHA.3492@.TK2MSFTNGP14.phx.gbl...
> I have a report that is launched from a windows app using URL access. Is it possible to turn off the parameters section of the toolbar without turning off the other options. I have tried using &rc:Toolbar=False but tis turns off the entire toolbar and not just the paramater input boxes. Any ideas?

How to summarize a text filed ?

Greetz!

I need to perform a SUM on a text field in CR 8.0. I tried creating a formula filed which contained something like this: SUM {textfield.text} but the report said: Unknown filed name. Now, the text field ( crystal reports text object actually ) is not a database field, but a plain text box which contains certain data. Can this be achieved at all ? Summarizing or any other function over a text box ?

Thnx.Hi

It is not possible to use SUM () for a non database field.|||Ok. Well, is there an alternative ? This text box mentioned earlier contains a vital data which I collected from a foreign source (used a text driver in VB to read it from a text delimited file ), and I can display it's value in a details section, but as I said earlier, I cannot SUM it. So .. if there's an alternative approach using crystal, I'd more then appritiate it.

How to store large text-strings in a sql-field?

I will store text with more than 8.000 characters in a sql-database. I defined my field as data-type=text. But when I paste a large text string into the database, only some of the text is being saved. The text also changes to "<long text> so I can't change it.

I tried to make some asp-code to handle the problem, but I can't find the right constants to use according to the data-type=text. In my example I use the constant "adBinary".

strInfo = Request.Form("txtInfo")
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = MyConn
.CommandText = "add_Info"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter ("@.Info", adBinary, adParamInput, , strInfo)
.Execute lngRecs, , adExecuteNoRecords
Set cmd = Nothing
End With

Is there anybode that can help me?I think you are using SQL-SERVER,
First check the maximum size allowed in TEXT type of Field.

I doubt if it can allow so many characters.|||I found the solution. In the asp-code, I use datatype "varchar" and number of characters like 2147483647.

How to store file attachments (text and binary).

I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?

Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.

CREATE TABLE dbo.[CM_Attachment] (
[Id] int IDENTITY (1, 1) NOT NULL ,
[Change_Request_Id] int NOT NULL ,
[Attachment] varbinary(max) not null,
[Created_Date] datetime NOT NULL DEFAULT GetDate() ,
[Created_By] varchar(30) NOT NULL ,
[Modified_Date] datetime NOT NULL ,
[Modified_By] varchar(30) NOT NULL
)
GO

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB) -- Does not work
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob) -- Works
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob) -- Works
as ChangeRequestAttachment

I believe the datatype to use is "image" and it will store any file type.

Check out the link below:
http://www.aspnettutorials.com/tutorials/database/Save-Img-ToDB-Csharp.aspx

|||

2 quotes from "Microsoft SQL SERVER 2005 Implementation and maintenance Training Kit":

[

Binary Data Types

The most popular data type within this group is the image data type. This data type has an unfortunate name; it is not used exclusively to store images, such as a library of pictures from a recent vacation. Although you can store pictures in an image data type, you can also use this data type to store Word, Excel, PDF, and Visio documents. You can store any file that is 2 GB or less in size in an image data type. One of the most famous implementations of this data type is the TerraServer project, which is a multiterabyte database of terrestrial images that you can access at www.terraserver.com.

The varbinary(max) data type is new to SQL Server 2005. It can store the same amount of data as an image data type, and you can use it with all the operations and functions that you can use with binary/varbinary data types.

]

... and ...

[

Microsoft intends to drop support for the text, ntext, and image data types in upcoming SQL Server versions. For this reason, Microsoft recommends that you stop using these data types.

]

|||Can you please explain what you mean when you say it doesn't work for text? You would simply read it as SINGLE_BLOG and insert into varbinary(max) column. For text files, the data will be a binary stream so you should be able to simply cast it to varchar(max) or nvarchar(max) to read or see the text data. SQL Server doesn't care what you put inside varbinary(max) column or the nature of the content. The server simply sees the data as a stream of bytes.|||

Thank you everyone for your help thus far. Unfortunately I'm not quite there yet.

When trying to store a text file using SINGLE_BLOB my result is
(0 row(s) affected)

When trying to store a text file using SINGLE_CLOB my result is
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

SQL

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
--Results in the following:
--(0 row(s) affected)

-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
-- Results in this error:
-- Msg 257, Level 16, State 3, Line 1
-- Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

-- These two work.
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob)
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob)
as ChangeRequestAttachment

|||

I found my mistake! The text file I was testing with was a zero byte file (I forgot to put some text in the test file). Once I added some text to the test Text file the following two insert statements worked correctly.

-- This works using SINGLE_CLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, Convert(varbinary(max), BulkColumn) , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
as ChangeRequestAttachment

-- This works using SINGLE_BLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
as ChangeRequestAttachment

Thank you again everyone for your help; what a great resource this form is.