Converting a Column in XML

Hello NG!
We would like to convert an Column which contain XMLs to an XML Column.
Actually it ist formatted as plain Text in a big Table with approx. 300.000
Lines.
Trying to convert the Type of the Column to XML does not succed because of a
TimeoutError everytime we try it.
Do someone has an idea how we can solve this Problem?
Thank xou very much
MarkusHave you tried exporting/splitting your table into say, 3 or 6 equal parts
as physical temp tables, convert your field into XML there, and then
re-import into the orginal table? You might need to drop some FKs to do
this.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Markus Nistim" <manistim@.hotmail.com> wrote in message
news:%23ZqoniMNIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hello NG!
> We would like to convert an Column which contain XMLs to an XML Column.
> Actually it ist formatted as plain Text in a big Table with approx.
> 300.000 Lines.
> Trying to convert the Type of the Column to XML does not succed because of
> a TimeoutError everytime we try it.
> Do someone has an idea how we can solve this Problem?
> Thank xou very much
> Markus
>

Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.

Convert Time to 24 Hour Clock

All ,

I need to convert the given time into 24 hour clock .

I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.

Please help ..

Regards,

Ashish

try this one..

SELECT CONVERT(varchar,GETDATE(),114), CONVERT(varchar,convert(smalldatetime,'1/1/2007 7:00:00 PM'),114)|||

Ashish:

What exactly do you mean by "... one contain time in 12 Hr clock ..." what is the datatype of that particular column? The standard SQL Server "DATETIME" datatype stores data such that if you specify incoming time with a 12 Hr clock going in it is still be stored in the same internal way as time that is specified incoming with a 24 hour clock. If both columns are stored as DATETIME datatypes you can compare them directly without needing any conversion.

Kent

|||

Hi Kent ,

Here is the content of the two table and both data types are varchar

Table 1 Table 2

Col Col

12.00a 1200

1.00p 1300

2.30p 1430

Now the problem is i can directly add 12 to the table 1 values by taking substring and converting it into integer but here i dont have 00.00 for 12 Am rather i have 12.00a for the same so if i add 12 to 12.00a it became 24.

I am not able to get if i can use case in where but seems it is not possible , Do you know any other way to do it.

Regards,

Ashish

|||

Ashish,

In order for us to provide you the best suggestions, please post the DDL for both tables.

|||

( Have you got this one then, Arnie? Thanks, Arnie; I'll go ahead with it then.)

Question: How does 12:30 AM appear for table 2 (24 hour clock)

Also, you show '12.00a' on the 12 hour clock translates to '1200' will you please verify this? This looks to me like an error.

|||Nope, just thought the question should be asked.|||

Code Snippet

declare @.table1 table (time1 varchar(6))
declare @.table2 table (time2 varchar(4))
insert into @.table1
select '12.00a' union all
select '12.30a' union all
select '1.00p' union all
select '2.30p'
--select * from @.table1

insert into @.table2
select '0000' union all
select '0030' union all
select '1300' union all
select '1430'
--select * from @.table2

select a.time1,
b.time2
from ( select case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)
as time1
from @.table1
) a
join @.table2 b
on a.time1 = cast(b.time2 as integer)

/*
time1 time2
-- --
0 0000
30 0030
1300 1300
1430 1430
*/

Now, there is another piece of this. And that is that both of these are going to table scan as they are because indexes cannot be employed for lookups. What you REALLY need to consider is converting these tables so that the time values get stored in a standard fashion -- either as an integer or as a datetime datatype. This will give the optimizer an optimizer to employ indexes if they are available. And if you are frequently going to join these two tables based on time then you really should try to set it up so that this stuff gets indexed with a standard datatype.

(I don't think I need these reverses, but I don't have time to eliminate them either. Will somebody either check me on this or fix this?)

|||

I didn't even get out of the parking lot and I realized that the PARSENAME was just too much baloney. Hang on and I'll rectify. Something like this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
cast((100*convert(numeric(7,2), reverse(substring(reverse(time1), 2, 5))))%1200 as integer)

is better than this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)

|||

Here are the values in first table

ID Begin End

Time Time

1 12:00a 12:29a 2 12:30a 12:59a 3 1:00a 1:29a 4 1:30a 1:59a 5 2:00a 2:29a 6 2:30a 2:59a 7 3:00a 3:29a 8 3:30a 3:59a 9 4:00a 4:29a 10 4:30a 4:59a 11 5:00a 5:29a

Here is the Entries in second table

Begin Time

133502 133522 133502 133522 123502 123522 123502 123522 123504 123524 123505 123524 123502 123522 133522 123522 133522 124837

I need to compare Begin Time of first table to Second table and make a join

|||

I think that I see where this is going...

The next part of the problem will be how to group Table2 in the groups of Table1. (Every half-hour.) This is NOT going to stop with a simple join between disparate column datatypes...

**********************************************************************************

>>>> Ashish <<<-

It would be so much easier if you could change the datatypes for both tables to be shortdatetime datatypes.

Is that possible?

Otherwise, you are going to be saddled with incredible 'kludgy' code, and it will be increasingly difficult to create the kinds of information that you want to create from your data. Yes, we can help you hammer out some tortured code that will solve the current problem, BUT I don't think that you have fully explained where this is going. Get a grip and do it right -change the datatypes, then the solutions will be relatively simple! This is crazy making stuff and it is not going to get any better.

|||

Hi Arnie ,

I did either way around , Added one more column in the table and updated it with the corresponding conversion. I guess this will not increase any performance issue.

Your Thoughts....

Regards,

Ashish

|||

Ashish,

Thanks, that will greatly improve performance issues for handling the data in either a JOIN or an aggregation operation.

In your original post, you indicated that you wanted to JOIN the two tables. But your sample data does not indicate any columns with matching time values.

So is it a JOIN, or as I expressed earlier, are you really wanting to group by half-hour intervals -or something else?

Please clarify your desired results, and we can help you find a fast and efficient solution.

Convert RTF to plain text

I have a SQL Server 2000 table with a few fields of "text" data type
that contain rich text. I have to downstream this data and the
recipient cannot handle rich text. I need to figure out a way to
convert it back to plain text. Any suggetions?

TIAIt's painful, but you can loop thru every character. Use the ASCII
function to identify and remove any non-alphanumerics (0-9 or a-z or
A-Z or space or period or comma ...).|||"Ted" <teddy_theo@.yahoo.com> wrote in message
news:1108133581.962856.208040@.l41g2000cwc.googlegr oups.com...
>I have a SQL Server 2000 table with a few fields of "text" data type
> that contain rich text. I have to downstream this data and the
> recipient cannot handle rich text. I need to figure out a way to
> convert it back to plain text. Any suggetions?
> TIA

The best idea is probably to export the data to file and convert it
externally - the MSSQL string functions are extremely basic, and writing a
script in something like Perl, Python, C# or whatever will be much more
efficient. With a bit of Googling, you'll probably be able to find something
for your preferred language - there's already a Perl module, for example.

Simon|||agreed. i was trying to avoid a front end process because the client
is going to be pulling data directly from a view in a production
environment. i'll have to throw a little .net app together to do the
conversion i suppose. thanks for all the feedback!!|||"louis" <louisducnguyen@.gmail.com> wrote:

>It's painful, but you can loop thru every character. Use the ASCII
>function to identify and remove any non-alphanumerics (0-9 or a-z or
>A-Z or space or period or comma ...).

I've written software (as a standalone utility, not in the context of
SQL) that goes the other way, but can't offer anything that helps in
this direction. I can offer some advice though.

You need to be a bit more careful than outlined above. In RTF the
backslash "\" and brace characters "{}" are reserved. RTF is
essentially a markup language and the "tags" start with a backslash,
and can contain alphanumerics (typically alphas and then - optionally
- numerics). Braces are used to delimit sections. Some sections
such as those in the header (info and font tables) can be entirely
discarded from the visible output.

Braces and backslashes in the text are escaped - IIRC - with a
backslash. Using this, you could indeed convert most of the RTF
to text.

This approach would recover most text, but some features such as
lists might come out strange, and it wouldn't be formatted nicely,
unless you wanted to honour the \par and \line tags to give line
formatting, but in the context of insertion into a database I guess
that's the most you's want to do.

Also, depending on the source of the RTF you may be dealing with easy
to parse snippets, as opposed to a fully-featured document.

I should imagine that programming the above in SQL would be - as you
rightly point out - quite painful.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

Convert Punctuation to Spaces?

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> Hi,
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
> Thanks!

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @.SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:406e7002$1_1@.news.bluewin.ch...
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@.SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@.newsread1.news.pas.earth link.net...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:406e7002$1_1@.news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @.SearchIncludes) ')
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
> Thanks!

This may help:

http://oldlook.experts-exchange.com...Q_20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon|||> This may help:
>
http://oldlook.experts-exchange.com...Q_20711909.html
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!|||>"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
"Simon Hayes" <sql@.hayes.ch> replied in message
news:406e7002$1_1@.news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@.Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @.Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!

Convert date and time to string?

Hi,

Can anyone tell me how to convert date and time to string.

I got one field inside my database which contain data like below:

4/16/2004 10:19:01 AM

if i wan to call out a record which have to refer to the data above

Select *
From table_A
where field_date= ???

what to fill in the ??

ThanxMan, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP|||Originally posted by Pat Phelan
Man, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP

hi there again....

I tired like what you hav told me but i cant get any output from the query statement lo.its like the record doesn't exist.

its like the format i keyed in is not the same.

Can u pls explain .
thanx|||My guess would be that you are missing the milliseconds. The value in the database probably has milliseconds, but your constant does not.

Databases are finicky about things like that! ;)

-PatP