How to remove tabs from a field?

Is there any way to remove tabs from text fields? I see tabs at the end when I import the data in Excel.
Appreciate any helpSure, in the view that you are extracting use the Replace function, something like:CREATE VIEW vThingie
SELECT thingieId, Replace(thingieText, Char(9), ' ') AS d
FROM thingie
WHERE thingieId < 100...or something like that!

-PatP

How to remove new line characters?

I want to display the example record below in a text box without the hard
returns. How can I strip them out so the record displays in one nice line?
The hard return shows as a sqaure box in the sql table, however it does not
paste into this web form. Imagine a sqaure box at the end of each line
below...
Bilat heel and ankle pain when walking and running
Medial knee pain bilat
Anterior compartment strain bilat
Bialt hips hurt w/ occassional low back painreplace(yourTextLine, char(13), ' ')
"Chris Patten" wrote:
> I want to display the example record below in a text box without the hard
> returns. How can I strip them out so the record displays in one nice line?
> The hard return shows as a sqaure box in the sql table, however it does not
> paste into this web form. Imagine a sqaure box at the end of each line
> below...
> Bilat heel and ankle pain when walking and running
> Medial knee pain bilat
> Anterior compartment strain bilat
> Bialt hips hurt w/ occassional low back pain

How To Remove Leading Zeros

Hi
I need to remove leading zeros from a text string of numbers. For example:-
'000012' needs to be '12'
'000123' needs to be '123'
'012345' needs to be '12345'
'008000' needs to be '8000'
'01203' needs to be '1203'
There can be between 1 and 4 zeros at the front of the string. Removing all
zeros is not difficult using String Functions, but I only need to remove the
leading zeros. Does anyone have any suggestions?
Thanksif there are no blanks in the original string, then try this:
select replace(ltrim(replace('001100120','0',' ')),' ','0')
BTW "text string of numbers" might be a database design flaw|||Here is a brute force method.
select replace(ltrim(replace('000012','0',' ')),' ','0')
select replace(ltrim(replace('000123','0',' ')),' ','0')
select replace(ltrim(replace('012345','0',' ')),' ','0')
select replace(ltrim(replace('008000','0',' ')),' ','0')
select replace(ltrim(replace('01203','0',' ')),' ','0')
Payson
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Convert to int (if all the fields are numbers only of course)
declare @.value varchar(20)
select @.value ='000012'
select convert(int,@.value)
http://sqlservercode.blogspot.com/|||convert to int and back to varchar
e.g.
declare @.x table (col1 varchar(6))
insert into @.x
select '000012' union all
select '000123' union all
select '012345' union all
select '008000' union all
select '01203'
select convert(varchar, convert(int, col1)) as Stripped
from @.x
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Thanks guys, some good ideas here - I'll try them out in the morning.
You may be right, Alex, about the design flaw, but it's not my database!
"Trey Walpole" wrote:

> convert to int and back to varchar
> e.g.
> declare @.x table (col1 varchar(6))
> insert into @.x
> select '000012' union all
> select '000123' union all
> select '012345' union all
> select '008000' union all
> select '01203'
> select convert(varchar, convert(int, col1)) as Stripped
> from @.x
>
> andrew wrote:
>|||Once again, thanks for the suggestions, guys. Just for the record,
converting to Integer data type seems the easiest way to go:
SELECT CAST(field_name AS int) AS alias FROM table_name
"andrew" wrote:
> Thanks guys, some good ideas here - I'll try them out in the morning.
> You may be right, Alex, about the design flaw, but it's not my database!
>
> "Trey Walpole" wrote:
>

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks

How to refresh Full Text Indexes?

Hi All,

i am using full text search in stored procedure for my web application search-engine.

how do i refresh my fulltext indexes which i created using unique indexes?

as i can see two options tart full population and Start Incremental population in full-text index on right click of table.

on click of anyone its showing successful.

can anyone please let me know what is the standard way to refresh full text indexes periodically in terms of best performance? i found, when i run query first time with contains keyword , it takes much time.

what enterprise settings required to increase performance?

Thanks in advance.

When you start a population of a fulltext catalog, it reports successful but that only means its started successfully. The population can take a while depending on size of columns, number of rows etc.

Depending on the size of the index and how long it takes to do a full population, you may want to do run regular incremental populations which will only update the records that have been added/changed since the last full population. Population is quite a resource intensive process so be aware of what effect it has on your application. Also remember you'll need to add a timestamp field to any tables which you want to do incremental populations on.

If you need to realtime updates to happen, look into enabling change tracking/background update which will keep your index in sync with the data. Otherwise, new/changed rows won't be reflected in your fulltext index until you do another population.

As with any index, as data gets added and removed the index becomes fragmented so i'd look to schedule a rebuild of the catalog periodically (how frequent is up to you).

The first query may have been slow as the population may still have been in progress. Look at FULLTEXTCATALOGPROPERTY to check the size of the index and the current population status.

HTH!

|||

Hello,

thanks a lot for the reply.

can you please verify the way how i created Index?

create fulltext catalog MyFullTextCatalog

create unique index ui_AssetID on EMAM_Assets(ID)

create fulltext index on EMAM_Assets(title,asset_desc) KEY INDEX ui_AssetID on MyFullTextCatalog
with CHANGE_TRACKING AUTO

i created full text index as mentioned above. i want to perform search on title and asset_desc.

I created one schedule (daily) on table -> full text properties to populate full text index. I didnt get your point about timestamp. where do i need to add and how to use it? where will it be helpful?

second, i click on Full text Catalog property, its showing error: Property FullTextIndexSize is not available for FulltextCatalog. This property may not exist or may not be retrieved. once i click on OK on this error alert, its opening Full Text Catalog Property. Is it something wrong?

Please guide me in set up Administration configuration for Full Text Index. I am working on Search Engine and Query performance is very much important. i having 5 tables on which i need to perform search.

|||

You need to add a new column to the table you are running incremental populations on with a datatype of TIMESTAMP, the value of which is controlled by SQL Server to keep track of changes to records in the table.

With the commands you have used, SQL Server should populate the catalog as soon as the index is created. Rather than using the GUI to check the property, use FULLTEXTCATALOGPROPERTY in a query window (see Books Online for more details). The error you get may be related to the GUI rather than your full text catalog.

Have a look at the following link which has some great tips on Full Text Searching:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx

HTH!

|||

Hi,

thanks for valuable input.

I created full text index with Change Track Automatically.

As i understand, in that case i need not to worry about full text population. is that right?

Full text index refresh will be done automatically. right?

let me know if it is required in this case also.

i already removed schedule for full text index population.

now i am facing on big problem is that: Query taking around 1 min while executing first time.

after that if u will be sit ideal for around 20 min then once again query will take more time.

instant execution followed by first one will result very fast.

I will check for cache memory and I/O for that. i think it could be reason.

Please let me know if you have any idea.

Thanks.

|||

You're right- once you've enabled change tracking a Full Population should happen automatically and the index will be maintained by sql server.

Make sure that the initial full population of the catalog has completed before running a query as it will be quite a resource intensive process and may be the cause of your slow query,

Good luck!

How to refresh Full Text Indexes?

Hi All,

i am using full text search in stored procedure for my web application search-engine.

how do i refresh my fulltext indexes which i created using unique indexes?

as i can see two options tart full population and Start Incremental population in full-text index on right click of table.

on click of anyone its showing successful.

can anyone please let me know what is the standard way to refresh full text indexes periodically in terms of best performance? i found, when i run query first time with contains keyword , it takes much time.

what enterprise settings required to increase performance?

Thanks in advance.

When you start a population of a fulltext catalog, it reports successful but that only means its started successfully. The population can take a while depending on size of columns, number of rows etc.

Depending on the size of the index and how long it takes to do a full population, you may want to do run regular incremental populations which will only update the records that have been added/changed since the last full population. Population is quite a resource intensive process so be aware of what effect it has on your application. Also remember you'll need to add a timestamp field to any tables which you want to do incremental populations on.

If you need to realtime updates to happen, look into enabling change tracking/background update which will keep your index in sync with the data. Otherwise, new/changed rows won't be reflected in your fulltext index until you do another population.

As with any index, as data gets added and removed the index becomes fragmented so i'd look to schedule a rebuild of the catalog periodically (how frequent is up to you).

The first query may have been slow as the population may still have been in progress. Look at FULLTEXTCATALOGPROPERTY to check the size of the index and the current population status.

HTH!

|||

Hello,

thanks a lot for the reply.

can you please verify the way how i created Index?

create fulltext catalog MyFullTextCatalog

create unique index ui_AssetID on EMAM_Assets(ID)

create fulltext index on EMAM_Assets(title,asset_desc) KEY INDEX ui_AssetID on MyFullTextCatalog
with CHANGE_TRACKING AUTO

i created full text index as mentioned above. i want to perform search on title and asset_desc.

I created one schedule (daily) on table -> full text properties to populate full text index. I didnt get your point about timestamp. where do i need to add and how to use it? where will it be helpful?

second, i click on Full text Catalog property, its showing error: Property FullTextIndexSize is not available for FulltextCatalog. This property may not exist or may not be retrieved. once i click on OK on this error alert, its opening Full Text Catalog Property. Is it something wrong?

Please guide me in set up Administration configuration for Full Text Index. I am working on Search Engine and Query performance is very much important. i having 5 tables on which i need to perform search.

|||

You need to add a new column to the table you are running incremental populations on with a datatype of TIMESTAMP, the value of which is controlled by SQL Server to keep track of changes to records in the table.

With the commands you have used, SQL Server should populate the catalog as soon as the index is created. Rather than using the GUI to check the property, use FULLTEXTCATALOGPROPERTY in a query window (see Books Online for more details). The error you get may be related to the GUI rather than your full text catalog.

Have a look at the following link which has some great tips on Full Text Searching:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx

HTH!

|||

Hi,

thanks for valuable input.

I created full text index with Change Track Automatically.

As i understand, in that case i need not to worry about full text population. is that right?

Full text index refresh will be done automatically. right?

let me know if it is required in this case also.

i already removed schedule for full text index population.

now i am facing on big problem is that: Query taking around 1 min while executing first time.

after that if u will be sit ideal for around 20 min then once again query will take more time.

instant execution followed by first one will result very fast.

I will check for cache memory and I/O for that. i think it could be reason.

Please let me know if you have any idea.

Thanks.

|||

You're right- once you've enabled change tracking a Full Population should happen automatically and the index will be maintained by sql server.

Make sure that the initial full population of the catalog has completed before running a query as it will be quite a resource intensive process and may be the cause of your slow query,

Good luck!

How to reference a text box in an insert statement

I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.

Here is the code:

Imports System.Data
Imports System.Data.SqlClient
Partial Class County_ConversionTest
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim v1 As Integer
Dim v2 As Integer
'Dim v3 As Integer
v1 = Integer.Parse(TextBox1.Text)
v2 = Integer.Parse(TextBox2.Text)
TextBox3.Text = (v1 + v2)

End Sub

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim strconn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\HCBS\App_Data\DivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim cnnDivAging As SqlConnection = New SqlConnection(strconn)
Dim v1 As Integer
Dim v2 As Integer
Dim v3 As Integer
v1 = CInt(TextBox1.Text)
v2 = CInt(TextBox2.Text)
v3 = CInt(TextBox3.Text)

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',v1,v2,v3)"

Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
cmdUpdates.Connection = cnnDivAging
cnnDivAging.Open()
cmdUpdates.CommandType = CommandType.Text
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()
End Sub
End Class

Here is the error message -

The name "v1" is not permitted in this context. Valid expressions areconstants, constant expressions, and (in some contexts) variables.Column names are not permitted.hey jcahill,
try this:

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George'," & v1 & "," & v2 & "," v3 & ")"

v1, v2, v3 are not there in sql, you just want the values in the sql.
Hope it helps.|||

You should get in the habit of doing it the right way:

Dim strsql AS String="INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',@.v1,@.v2,@.v3)"
Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
' cmdUpdates.Connection = cnnDivAging -- Redundant
cmdUpdates.Parameters.Add("@.v1",sqldbtype.int32).value=v1
cmdUpdates.Parameters.Add("@.v2",sqldbtype.int32).value=v2
cmdUpdates.Parameters.Add("@.v3",sqldbtype.int32).value=v3
cnnDivAging.Open()
' cmdUpdates.CommandType = CommandType.Text -- Redundant and should be done before the open to minimize the amount of time the connection is "open"
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()

sql

How to refer to report textbox values in another textbox

I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?

fields!textbox1.value doesn't work..what does?

Hello,

Try this:

ReportItems!textbox1.Value

Hope this helps.

Jarret

How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to read contents of text file from t-sql ?

Hi friends,
I need to read content of text file and store them in
string variable in my store procedure, Is there t-sql or
function to read content of text file ?
Thanks.There is no direct set of functions to do it, but you could use the sp_OA*
set of procedures to access the file. For an example of how to use these
procedures see:
http://www32.brinkster.com/srisamp/sqlArticles/article_32.htm
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Kresna Rudy Kurniawan" <kresna_rk@.yahoo.com> wrote in message
news:081001c3a377$c35948b0$a601280a@.phx.gbl...
> Hi friends,
> I need to read content of text file and store them in
> string variable in my store procedure, Is there t-sql or
> function to read content of text file ?
>
> Thanks.

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.

how to query for text containing parens?

I have an SQL database with rows that have parens in the data.

If I run a select statement such as:

SELECT SongName
FROM Songs
WHERE SongName = 'John Jacob (Jingleheimer Schmidt)'

It returns zero rows. This also:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob (Jingleheimer Schmidt)%'

returns zero rows.

If I change it to this:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob%'

Then I get the row returned.

Is there a way to use the first query example above and return the row?
I'm guessing it has something to do with the parenthesis...

*** Sent via Developersdex http://www.developersdex.com ***I figured out what the problem is, now how to come up with a solution.

The problem seems to be that the last [space] is being represented with
A0 instead of 20 in the database.

How can I take that into account in my queries and return the data
regardless of the byte value used for [space]?

*** Sent via Developersdex http://www.developersdex.com ***|||On 29 Sep 2006 20:31:59 GMT, Terry Olsen wrote:

Quote:

Originally Posted by

>I figured out what the problem is, now how to come up with a solution.
>
>The problem seems to be that the last [space] is being represented with
>A0 instead of 20 in the database.
>
>How can I take that into account in my queries and return the data
>regardless of the byte value used for [space]?

Hi Terry,

Short-term solution:

WHERE REPLACE (SongName, CHAR(160), ' ') = 'John Jacob (Jingleheimer
Schmidt)'

Downside is that an index on the SongName column (if there is any) can't
be used as effectively.

Long-term solution: fix the front end or the stored proc that handles
data entry to convert char(A0) to space (= fixing the leak), then run an
update to convert existing data (= mopping up the floor).

--
Hugo Kornelis, SQL Server MVP