Parameterized Expression: parsing

Showing posts with label parsing. Show all posts

Friday, March 30, 2012

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]..
.
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]
..
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfran
You probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Wednesday, March 28, 2012

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84
If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84
|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!
|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
--
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:
>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:
>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Variable Length Delimited Records

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Varchar2

I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.
Is there a sql function or statement to do this. Any help is greatly appreciated.
andavianselect to_number(substr(substr('$40-35' ,2),1,2)), to_number(substr(substr('$40-35' ,2),4,2)) from dual

is your select statement to parse out your 2 dollar amounts.

Originally posted by andavian
I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.

Is there a sql function or statement to do this. Any help is greatly appreciated.

andavian|||This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?|||Yes, this is because you have given a fixed length variable and did not mention about how dynamic your VARCHAR would be. This assumes ur variable will be either ''$40-35" or "$40-35" which you have mentioned in your Question.
Originally posted by Rushi
This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?

parsing varchar fields

InvoiceNumber@.VendorAcronym

There'd be a lot of vendors.

Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:

InvoiceNumber@.@.VendorAcronym

InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym

etc. -- something like that.

I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.

Thoughts?

ScottScott,

Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.

HTH

Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.

John

Jerry, John -- sorry for the lack of clarity.

We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).

Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.

So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).

Scott|||Hi

I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.

If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@.t eranews...
> Jerry Spivey opined thusly on Sep 24:
>> Scott,
>>
>> Instead of changing the existing product's tables why not create an
>> additional table(s) to store these values and then write your queries
>> using
>> joins.
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic code
> rather than exact values in two separate fields.

Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowing
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.

Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.

The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.

Scott|||Hi

If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.

John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@.t eranews...
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> I am not sure why you think the users would want to enter some cryptic
>> code
>> rather than exact values in two separate fields.
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>> That is more of a usability
>> with the UI, for instance if your parts were both numeric you may be
>> slowing
>> your users down forcing them to use non-keypad characters that require
>> shifting. You should also look at the business processes, for instance if
>> you processed everything for one vendor together then retaining the
>> vendor
>> information would remove the need to type it in again.
>>
>> If you need to concatenate the values for display purposes then you can
>> do
>> that in the code for your stored procedure or create a view.
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.

Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."

Good grief. Outta here.

Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.

Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.

Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scott Marquardt opined thusly on Sep 24:
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> If you are not writing a new interface then you are probably better using a
>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
>> a fixed length then you don't need a separator, but it may help the users
>> differentiate the different parts. I would have expect that you can
>> determine the fact that an invoice is a referral by a different means.
> Well, we could get into "I would have expected" exchanges, or we can skip
> the stuff I already know -- and posted in the original post. With all due
> respect, "duh."
> Good grief. Outta here.

John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.

"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

Scott|||>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<

Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.|||Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu|||Hi Erland/Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> If you are not writing a new interface then you are probably better
>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>> each part is a fixed length then you don't need a separator, but it may
>> help the users differentiate the different parts. I would have expect
>> that you can determine the fact that an invoice is a referral by a
>> different means.
>
> Now, how is that for a user interface? This is free text, and users will
> mangle it anyway, but they have better odds with a separator than fixed
> length.

That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.

> I have not intervened into this thread before, because I don't have
> much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.

> Hm, possibly could have a trigger on the table that loaded a new table
> with the fields split in pieces. Still iffy with regards to the vendor,
> but at least no existing object is changed. (Then again, being on the
> vendor side of the fence, I know what I would think if I found that one
> of our customers had added a trigger to one of our tables.)

Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nothing seems to be going right in this thread!

Who is Steve?

Sorry Scott!

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43364302$0$14057$da0feed9@.news.zen.co.uk...
> Hi Erland/Steve
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
>> John Bell (jbellnewsposts@.hotmail.com) writes:
>>> If you are not writing a new interface then you are probably better
>>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>>> each part is a fixed length then you don't need a separator, but it may
>>> help the users differentiate the different parts. I would have expect
>>> that you can determine the fact that an invoice is a referral by a
>>> different means.
>>
>>
>> Now, how is that for a user interface? This is free text, and users will
>> mangle it anyway, but they have better odds with a separator than fixed
>> length.
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it
> for themselves.
>>
>> I have not intervened into this thread before, because I don't have
>> much advice to offer Steve in this dire situation.
>>
> It seems to me, that if this an off the shelf package the benefits has
> been lost some time ago, if this is a bespoke development then it has not
> been specified thoroughly enough.
>> Hm, possibly could have a trigger on the table that loaded a new table
>> with the fields split in pieces. Still iffy with regards to the vendor,
>> but at least no existing object is changed. (Then again, being on the
>> vendor side of the fence, I know what I would think if I found that one
>> of our customers had added a trigger to one of our tables.)
> Writing a trigger may still invalidate the contract as the transactions
> will be effected, the vendor may have the right to charge improportate
> fees to fix a problem even if a problem is not related to this table. But
> if Steve has already tweeked things that could already be the case! A
> trigger would be useful for validation even if the replication route is
> not taken. Then again Steve was worried about upgrades breaking things.
> Dire may be an understatement!
> Steve has my sympathy.
> John
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techin.../2000/books.asp
>>|||Hi Scott

Non taken!

You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.

Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.

I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@.t eranews...
> Scott Marquardt opined thusly on Sep 24:
>> John Bell opined thusly on Sep 24:
>>> Hi
>>>
>>> If you are not writing a new interface then you are probably better
>>> using a
>>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part
>>> is
>>> a fixed length then you don't need a separator, but it may help the
>>> users
>>> differentiate the different parts. I would have expect that you can
>>> determine the fact that an invoice is a referral by a different means.
>>
>> Well, we could get into "I would have expected" exchanges, or we can skip
>> the stuff I already know -- and posted in the original post. With all due
>> respect, "duh."
>>
>> Good grief. Outta here.
> John., I'm going to apologize for a short temper there. It was born of
> impatience.
> My principal interest was in hearing how anyone else might PARSE the field
> to recover the data. Erland's trigger idea isn't bad for WHAT I do with
> it.
> "Invoice for 1/2 inch puce widgets and a can of beans,
> 100-3123@.WidgetMart"
> "Refund for can of beans, 100-3123&WidgetMart"
> The second one would need to be tagged as a referral to the first one. But
> if that's my basic technique, they're parse out the same way.
> --
> Scott|||--CELKO-- opined thusly on Sep 24:
>>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
> Since free text requires human intelligence, we get people to encode
> it. If the text is short and styled the same way, you can find AI
> programs that are pretty good these days. Of course this has nothing
> to do with RDBMS and nobody in their right mind would try to do it in
> SQL.

Hey! How'd you find out about my condition?

;-)

Scott|||Stu opined thusly on Sep 24:
> Scott,
> Have you considered an ActiveX script in a DTS package to shred the
> data periodically (you can use the SQL Scheduler to run it once a
> minute). We do this to parse out syslog tables; it's kludgy, but its
> consistent. While not as immediate as a trigger to populate a table,
> it does keep you from modifying the vendor database (which, if your
> vendor has the ability to update their database, could be a big
> benefit).
> Obviously, your users would have to be trained on how to enter data
> consistently, and you'd probably want some sort of validation to pick
> up on coded entries that don't meant your
> data-followed-by-a-comma-followed-by-more-data rules. You may even
> look at Regular Expressions inside your DTS package to help with that.
> Just thinking aloud,
> Stu

Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.

Scott|||John Bell opined thusly on Sep 25:

> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it for
> themselves.

In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)

The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.

Steve ;-)|||Scott Marquardt at wit's-end:

> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
> unless you'd care to name some good controls. Haven't tried any, ever,
> really. It's been nice to have never needed to!

> I anticipate we'll see now more than 3000 such records a year, so this is
> probably something that could be done at runtime. Users won't be running
> anything that queries a result of this, so parsing it all out when running
> reports (not sure where to implement that, yet, but it won't be in the
> application itself) is an inelegant but tenable option.

More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/sc...er/default.mspx

It'll either drive you over the edge, or restore your sanity.

GeoSynch|||GeoSynch opined thusly on Sep 27:
> Scott Marquardt at wit's-end:
>> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
>> unless you'd care to name some good controls. Haven't tried any, ever,
>> really. It's been nice to have never needed to!
>> I anticipate we'll see now more than 3000 such records a year, so this is
>> probably something that could be done at runtime. Users won't be running
>> anything that queries a result of this, so parsing it all out when running
>> reports (not sure where to implement that, yet, but it won't be in the
>> application itself) is an inelegant but tenable option.
> More stuff to drive you even crazier - Log Parser from Microsoft:
> http://www.microsoft.com/technet/sc...er/default.mspx
> It'll either drive you over the edge, or restore your sanity.

No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.

That's the world of education, folks! ;-)

Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)

Scott|||Scott Marquardt opined thusly on Sep 25:
> John Bell opined thusly on Sep 25:
>> That was my point, guess I should have said it that way! The users will
>> (probably) not like having to enter a coded string, even if you keep it as
>> simple! They may even deliberately mangle it if there is no value in it for
>> themselves.
> In this case, the only users needing to enter these have the only stake in
> these particular data points. This isn't something they'll grumble about;
> in this case, they're the ones demanding it. ;-)

OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.

| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@.',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@.',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@.',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@.',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@.',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@.@.',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@.%'

That translates into this:

This allows for putting an invoice@.vendor (or invoice@.@.vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.

These work:

invoice@.vendor yada yada yada
yada yada yada invoice@.vendor
yada yada yada invoice@.vendor yada yada yada
yada yada yada invoice@.vendor yada yada yada
invoice@.vendor
invoice@.vendor [with spaces at the end as well]

These will not work:

There are problems with this invoice@.vendor!
Use the invoice that was not paid (invoice@.vendor)

Also, you can't use more than one such thing in a single record's
description.

Scott

parsing varchar fields

What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

parsing Vabinary contnt

Where to find the syntax for parsing varbinary data in SQLserver?
Thanks
BillDefine "parsing".
What kind of data is in this varbinary content? (binary representations of
native text, files of known type...)
ML
http://milambda.blogspot.com/|||I assume that there's a general syntax to read the column's content.
In this case, it's text data.
thanks
Bill
"ML" <ML@.discussions.microsoft.com> wrote in message
news:54909221-5023-4CF2-A5EC-79FAAA07C907@.microsoft.com...
> Define "parsing".
> What kind of data is in this varbinary content? (binary representations of
> native text, files of known type...)
>
> ML
> --
> http://milambda.blogspot.com/|||If you're parsing a delimited string of character values stored as varbinary
,
maybe you should look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
Of course, you'll have to change the datatype of the parameter and maybe
adapt the logic a bit.
ML
http://milambda.blogspot.com/

Parsing T-SQL is not validating table schemas

I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.

It seems that the parser does not validate that a tables schema is missing.

This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.

CREATE PROCEDURE [dbo].[Address_Load]
@.AddressID [int]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.intError int

BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @.AddressID

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END

-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @.intError = ERROR_NUMBER();

-- Log error here

RETURN @.intError;
END CATCH
END

The stored proc parses fine and gets saved to the database but when executing it I get the following

Msg 208, Level 16, State 1, Procedure Address_Load, Line 10

Invalid object name 'Address'.

Is there any way to change this so the parsing will generate an error and not allow this into the database?

Thanks,

Cory

The behavior is due to deferred name resolution/compilation of TSQL modules in SQL Server. You can look it up in BOL for more details. See link below for starters:

http://msdn2.microsoft.com/en-us/library/ms190686.aspx

|||

And when you decide you don't like the behavior, please go here and vote!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It is good about 1% of the time to be like this. However, the problem is the 2% of the time when you are coding and mistype a table name and it still compiles, only to find out later when you are (hopefully) testing :)

|||Is there a way to turn off Deferred Name Resolution?|||

Nope. That is what this feedback that I mentioned:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It really ought to be a settting you can ask for, not soemthing that is on all of the time.

Friday, March 30, 2012

parsing XML string in a text field

parsing XML string in a text field

Parsing XML

Parsing XML

Parsing XML

Wednesday, March 28, 2012

Parsing web URL's in SQL

Parsing web URL's in SQL

Parsing web URL's in SQL

Parsing Variable Length Delimited Records

Parsing Variable Length Delimited Records

Parsing Varchar2

parsing varchar fields

parsing varchar fields

Parsing values from sp_spaceused stored proc.

parsing Vabinary contnt

Parsing T-SQL is not validating table schemas

Parameterized Expression

Blog Archive

About Me