Formating Ntext

Hi,
I am using SQLServer 2000
I currently have a table with just one field in it of type (ntext), the
reason for this is there is a very large amount of data in it of an XML
format. The data looks something like below althought this is only a small
sample there is actually a lot more in this field:-
<Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
house in the UK now requires a very large deposit, more than 5 times the
amount that was necessary in 1999 <Paragraph/><House
Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locatio
n/>
<Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
What I want is to break out the data into columns so you have something like
Paragraph HouseType Price Location
Garage Bedrooms
To buy a house... Detached ￡300,000 Berkshire Yes 4
I want to ignore the data between the <Main> Tags
Generally I think I need to break the data up based on the foward slash and
work left from there, I think it's going to be based on a left/charindex
principal but I also have to do a convert to varchar in the first place as
you are unable to do much with the data in it's current data type.
If anyone can help it would be much appreciated.
Thanks PDAre you going to do this exactly once, or regularly?
In either case, I think it would be better to have an app pull the whole
value out, parse it using XML or RegEx or whatever, and generate the INSERT
statement or procedure call necessary to break the data out.
Going forward, it would probably be better to store the individual pieces of
data and build the XML when selecting. Or, if you can move to SQL Server
2005, you can use the new XML datatype, which provides a whole slew of
options to make your implementation choice harder.
A
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
> Hi,
> I am using SQLServer 2000
> I currently have a table with just one field in it of type (ntext), the
> reason for this is there is a very large amount of data in it of an XML
> format. The data looks something like below althought this is only a
> small
> sample there is actually a lot more in this field:-
> <Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
> house in the UK now requires a very large deposit, more than 5 times the
> amount that was necessary in 1999 <Paragraph/><House
> Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locat
ion/>
> <Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
> What I want is to break out the data into columns so you have something
> like
> Paragraph HouseType Price Location
> Garage Bedrooms
> To buy a house... Detached ￡300,000 Berkshire Yes
> 4
> I want to ignore the data between the <Main> Tags
> Generally I think I need to break the data up based on the foward slash
> and
> work left from there, I think it's going to be based on a left/charindex
> principal but I also have to do a convert to varchar in the first place as
> you are unable to do much with the data in it's current data type.
> If anyone can help it would be much appreciated.
> Thanks PD|||Hi Aaron,
Thanks for the reply, I was going to go down the RegEx route but as this is
only a one of I just wanted to make it as quick and simple as possible so I
wanted to opt for querying it straight out of the field!!!.
Thanks PD
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you going to do this exactly once, or regularly?
> In either case, I think it would be better to have an app pull the whole
> value out, parse it using XML or RegEx or whatever, and generate the INSER
T
> statement or procedure call necessary to break the data out.
> Going forward, it would probably be better to store the individual pieces
of
> data and build the XML when selecting. Or, if you can move to SQL Server
> 2005, you can use the new XML datatype, which provides a whole slew of
> options to make your implementation choice harder.
> A
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
>
>

formating integer

hello. A question please. I am new on Sqlserver. Could anyone say me how must I format an integer to string. example

I have:

0001 (integer field of 4 positions) and I'd want to see it "0001" (string)

Thanks...

It seems like there is a better way of doing this, but one way is something like:

select right('000'+convert(varchar(4), 1), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0001

|||

Thanks. The problem is that I gave an example for the first field. (0001). But It could be 0010...

|||

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

|||

Waldrop wrote:

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

Nothing. That works fine.

SELECT right('0000' + cast([your_column] as varchar(4)),4)
FROM your_table
|||

I'm NEW !!!! Sorry !!!!. Another question please. I must put your statement in a string var. How can I do the '000' in my string var ?

Thanks...

|||

declare @.myString varchar(4)

set @.myString = right('000'+convert(varchar(4), 7), 4)

set @.myString = right('0000' + cast([your_column] as varchar(4)),4)

set @.myString = '0007'

|||

I'm so sorry. I don't understand. I forgot to say that the statement goes in an store procedure. My problem is how do I put the (') in a string var.

Thanks...

|||

An example of adding quote characters into the string variable:

declare @.myString varchar(6)
declare @.quoteChar char(1)

set @.myString = '''0007''' -- Loads '0007' (including quotes) into @.myString variable
set @.quoteChar = char(39) -- Loads a quote char ' into @.quoteChar variable
set @.quoteChar = '''' -- Loads a quote char ' into @.quoteChar variable

select @.myString as [@.myString],
@.quoteChar as [@.quoteChar]

-- - Output: -

-- @.myString @.quoteChar
-- -
-- '0007' '

declare @.anotherString varchar(4)

set @.anotherString = '0007' -- Loads '0007' (without quotes) into @.anotherString variable
set @.myString = char(39) + @.anotherString + '''' -- concatenates quote characters around '0007'

select @.anotherString as [@.anotherString],
@.myString as [@.myString]

-- - Output: -

-- @.anotherString @.myString
-- --
-- 0007 '0007'

|||Thanks. It works !!!!!

formating in SqlServer vs Reporting Services

All else being equal, is it faster to use functions like ROUND and TRIM
in the SELECT statement of the query and let the db process it or to
set formating in the layout of the report and let Reporting Services
handle it?
In my case, all is not equal. We have the DB on a very powerful machine
that at sometimes is very overtaxed and runs smoothly at others. RS is
moving to a decently powerful server that it will have exclusively.
Thanks
Louis Ryder
SSRS Report DeveloperMy two cents. Formatting is a presentation concern. Rounding to 2 decimals
can be handled in the presentation layer (i.e., RS) using format strings.
TRIM isn't necessary unless you have some smokin' character columns...
-Tim
"Louis Ryder" <lroskind@.ryderauto.com> wrote in message
news:1152203807.627376.268390@.p79g2000cwp.googlegroups.com...
> All else being equal, is it faster to use functions like ROUND and TRIM
> in the SELECT statement of the query and let the db process it or to
> set formating in the layout of the report and let Reporting Services
> handle it?
> In my case, all is not equal. We have the DB on a very powerful machine
> that at sometimes is very overtaxed and runs smoothly at others. RS is
> moving to a decently powerful server that it will have exclusively.
> Thanks
> Louis Ryder
> SSRS Report Developer
>