Using SQL Server (T-SQL) To Parse Text
Jul 23, 2005
In MS Access 2000 if I have a String such as:
Column1
Delta CC: 123
Charley CC: 234
Foxtrot CC: 890
and I wanted to extact just the numbers in to a field called CC
I could use this formula in a calculated field:
CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)
resulting in:
CC
123
234
890
Any idea on what the code should be within a view in SQL Server?
also -- what is a good reference that can help with these types of
problems.
Any help appreciated!
RBollinger
View 2 Replies
ADVERTISEMENT
Apr 24, 2007
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.
I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)
For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.
<ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
View 2 Replies
View Related
Jul 23, 2005
I am trying to build a query on a SQL2000 text field which contains XMLstring. The query is like "select requestnumber from history whererequestnumber is like '%re1%'". As you can see in the following samplerecords, the xml string has database structure and the requestnumber isa node of the XML. I wonder if it is possible to have SQL server parsethis field and allow me to do the query. If not, any suggestion wouldbe appreciated as to how to store XML data in SQL2000. I am not sureif I misused the SQL2000 XML feature correctly. So far I pass the rawquery result to ADO and manipulate it in XMLDOM.The table is to capture history of changes in any record in mydatabase. So I need to keep it simple so any record from any table canbe 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</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>
View 1 Replies
View Related
Nov 10, 2014
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData
1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]
2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.]
3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set)
ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8
1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt
2 [abc1.Gray...abc8.] Gray Shoes Striped jacket
3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like:
DECLARE
@string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]',
@searchString1 varchar(20) = 'abc1',
@searchString2 varchar(20) = 'abc2';
SELECT newstring
FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
View 1 Replies
View Related
May 18, 2006
I've got some machines that output text files after each shot of parts. I'd like to take the data in those files and parse it and insert it into a SQL Server database for future massaging. The text files look like the example I've posted below. Can SSIS parse out the set points and actual values even though the file isn't CSV or tab delimited and the data is kind of 'strewn' all over the report? Each report does have the exact same format so the report format doesn't change from report to report, just the data. Thanks in advance.
Ernie
WP4.57 C Y C L E P R O T O C O L
Order data 18.05.06 11:27:57
Order number : 2006 Recipe no. : 15761
Machine number : 7 Recipe name : Stabilizer Bar Innsulator
Machine Operator: 1257 Art.descrip.: Stabilizer Bar Grommet
Shot Volume : 285.8
Part quantity : 100096 Type of mat.: M370-34
Shot quantity : 782 Batch number: 20124-125
-------------------------------------------------------------------------------
Temperatures in øC
Set Act Set Act
Fixed heat.platen right 182 182 Tempering screw 83 83
middle 180 180 Tempering inject.cylinder 85 85
left 182 182 Tempering circuit 3 90 91
Tempering circuit 4 0 39
Movab.heat.platen right 182 182 Tempering circuit 5 0 39
middle 180 180
left 182 182 Mould temperature 1 0 39
Mould temperature 2 0 39
Third heat.platen right 0 39 Mould temperature 3 0 39
middle 0 39 Mould temperature 4 0 39
left 0 39 Mould temperature 5 0 39
Mould heating circuit 6 0 39 Compound temp.after screw 104 104
Mould heating circuit 7 0 39 Compound temp.after nozzle 0 39
Mould heating circuit 8 0 39
Mould heating circuit 9 0 39
Mould heating circuit 10 0 39
---------------------------------------------------------------------------
Times in sec
Injection time 51.20 Transfer time 1 2.00
Internal mould press.time 0.00 Transfer time 2 2.00
Dwell pressure time 7.00 Transfer time 3 2.00
Controlled cure time 180.00 Transfer time 4 2.00
Calculated cure time 0.00 Transfer time 5 2.00
last cycle time 276
last opening time 25
---------------------------------------------------------------------------
Measure when injecting Measure at injection end
max. injection speed mm/s 11.9 Injection length mm 2.0
Injection energy kNm 247.2 Injection time sec 51.20
max. int.mould pres. bar 2 Hydraulic pressure bar 190
max. dwell pressure bar 192 Internal mould pressure bar 0
Pad mm 0.4
---------------------------------------------------------------------------
Stock Temperatures and Pressures During Metering
Stock Temperatures(C) Set Actual Metering Pressures(bar) Set Actual
Temperature 1st Step 105 106 Pressure 1st Step 135 131
Temperature 2nd Step 105 106 Pressure 2nd Step 135 129
Temperature 3rd Step 105 105 Pressure 3rd Step 135 122
Temperature 4th Step 105 106 Pressure 4th Step 135 135
Temperature 5th Step 105 109 Pressure 5th Step 135 137
Protocol Complete
View 1 Replies
View Related
Mar 30, 2005
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
View 3 Replies
View Related
May 26, 2007
I need to parse some text files and load them to database - these files are mostly CSV files or fix width columns format and the column names (first line) may vary in text (e.g. different abbreviation), order and extra columns, etc.
Is it a good idea have this done using script task of SSIS? How it compare to Perl on performance? Or any other tools good for this?
View 7 Replies
View Related
Jun 1, 2015
I have a data field in a SSRS Report that contains the requestor's User Id. Their ID is prefixed with "PRIV"...And I'm assuming that is the direct result of the network domain. I need to create a SSRS expression to determine if the User ID is prefixed with "PRIV" and then parse that out and use what's behind the "" as their true User Id.
example...."PRIVID123456" should appear as "ID123456" in the report data line.
View 5 Replies
View Related
Feb 26, 2008
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType
FROM dbo.CORP_EMP_IDS as corp_emp_ids
WHERE (EmployeeID BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')) AND (Date_Exp > GETDATE() OR
Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
UNION
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType
FROM dbo.CORP_EMP_IDS AS CORP_EMP_IDS_1
WHERE (CustAtr BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')) AND (Date_Exp > GETDATE() OR
Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
ORDER BY EmployeeID
View 11 Replies
View Related
Jan 28, 2015
How to parse a string to equal length substrings in SQL
I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..
CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp
After parsing I have to use these values in a where clause like this
IN('000001','0T0001','0L0001','000T01','0C0001')
Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.
View 2 Replies
View Related
Oct 22, 2014
I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
View 1 Replies
View Related
Jun 16, 2015
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
View 2 Replies
View Related
Sep 12, 2007
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
View 5 Replies
View Related
Apr 21, 2015
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies
View Related
Sep 25, 2007
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
View 2 Replies
View Related
Apr 15, 2008
-- Prepare sample data
DECLARE@h INT,
@XML VARCHAR(8000),
@2k5 XML
SELECT@XML ='
<SalesOrder xmlns:dt="urn:schemas-microsoft-com:datatypes">
<doc_gps_is_MC_trx dt:dt="i2">0</doc_gps_is_MC_trx>
<doc_gps_exchange_time dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_time>
<doc_cy_oadjust_subtotal dt:dt="string" Type="decimal">4</doc_cy_oadjust_subtotal>
<doc_gps_currency_index dt:dt="i2">1007</doc_gps_currency_index>
Avenue</doc_bill_to_street>
<doc_gps_exchange_table_id dt:dt="string" />
<doc_gps_misc_taxscheduleid dt:dt="string" />
<doc_gps_MC_transaction_state dt:dt="i4">0</doc_gps_MC_transaction_state>
<doc_ship_to_state dt:dt="string">MD</doc_ship_to_state>
<doc_bill_to_phone dt:dt="string">410-325-3531</doc_bill_to_phone>
<_Verify_With />
<doc_gps_currency_id dt:dt="string">Z-US$</doc_gps_currency_id>
<doc_bill_to_state dt:dt="string">MD</doc_bill_to_state>
<doc_ship_to_zip dt:dt="string">21206</doc_ship_to_zip>
<doc_gps_rate_calc_method dt:dt="i2">0</doc_gps_rate_calc_method>
<doc_gps_freight_taxable dt:dt="i2">0</doc_gps_freight_taxable>
<_Purchase_Errors />
<doc_currency_compatibility dt:dt="string">cy</doc_currency_compatibility>
<doc_bill_to_country dt:dt="string">United States</doc_bill_to_country>
<doc_cy_total_total dt:dt="string" Type="decimal">7.25000</doc_cy_total_total>
<doc_shipping_total dt:dt="i4">325</doc_shipping_total>
<doc_cc_number dt:dt="string">4828500531471028</doc_cc_number>
<doc_gps_expiration_date dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_expiration_date>
< <doc_cy_handling_total dt:dt="string" Type="decimal">0</doc_cy_handling_total>
<doc_bill_to_email <doc_gps_misc_taxable dt:dt="i2">0</doc_gps_misc_taxable>
<doc_ship_to_phone dt:dt="string">410-325-3531</doc_ship_to_phone>
<doc_cy_gps_freight_tax_included dt:dt="string" Type="decimal">0.0</doc_cy_gps_freight_tax_included>
<doc_gps_exchange_date dt:dt="datetime">1/1/1900 12:00:00 AM</doc_gps_exchange_date>
<doc_cy_gps_freight_tax_total dt:dt="string" Type="decimal">0.00000</doc_cy_gps_freight_tax_total>
<doc_cy_gps_misc_tax_total dt:dt="string" Type="decimal">0.00000</doc_cy_gps_misc_tax_total>
</SalesOrder>',
@2k5 = @XML
EXEC sp_xml_preparedocument @h OUTPUT, @XML
SELECTdoc_cy_oadjust_subtotal
FROMOPENXML (@h, ' WHAT WILL COME HERE')
WITH(
doc_cy_oadjust_subtotalVARCHAR(100)'Name'
)
EXEC sp_xml_removedocument @h
can anyone tell me what will come in that maroon coloured? - 'WHAT WILL COME HERE' - instead of '/campaignrequest/requestor'
like i saw one example in this forum like:
<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>
---------------------------------------------
DECLARE@h INT,
@XML VARCHAR(8000),
@2k5 XML
SELECT@XML ='
<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
<phone>555-555-1234</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>
',
@2k5 = @XML
EXEC sp_xml_preparedocument @h OUTPUT, @XML
SELECTemailaddress,
name,
company,
phone1,
phone2
FROMOPENXML (@h, '/campaignrequest/requestor')
WITH(
emailaddressVARCHAR(100)'emailaddress',
nameVARCHAR(100)'name',
companyVARCHAR(100)'company',
phone1VARCHAR(100)'phone',
phone2VARCHAR(100)'phone'
)
EXEC sp_xml_removedocument @h
thanks if someone help me to figure this out.
View 16 Replies
View Related
Jul 20, 2005
Hello,I have a table that has a name field with the following datajohn doejohn doe smithjohn d smithI need to separate the first, middle and last names into separatefields. ex. first varchar (20), middle varchar (20) and last varchar(20).I am testing the process that I have to follow by doing the followingcreate table names (name varchar (40),first varchar (20),middle varchar (20),last varchar (20))insert into names (name)values (john doe smith)I get an error message when I run the following query:update bset first = substring(name, 1, (charindex('', name)-1))The error message is:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.The statement has been terminated.Does anybody have any suggestions?TIAja
View 2 Replies
View Related
Jul 4, 2007
I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.
I am wondering what would be the best method of doing this, or if anyone has an example.
Thank you
View 6 Replies
View Related
Jun 4, 2007
Hi All!
I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.
Help!
Select '%,' into tblclients as strfirstname from tblclients
I know this won't work, but I want to show you all I'm at least trying!
View 6 Replies
View Related
Jun 7, 2007
This script parses the @@VERSION global variable into individual columns.
I developed it because I wanted to be able gather standard info on all versions.
I know there are functions for a lot of this, but only starting with SQL 2000.
It seems to work with all versions of SQL Server from 7.0 through 2005.
I haven't tested with 6.5 and before or 2008, because I don't have either available.
Please report any problems you see.
Edit: 2007-7-31
1. Changed SQL_SERVER_MAJOR_VERSION to varchar(20)
2. Added code to create a view named V_SQL_SERVER_VERSION
3. Added four new columns to the view:
SERVER_NAME, value from @@servername
SQL_SERVER_MAJOR_VERSION_NUMBER, Example: 9
SQL_SERVER_VERSION_NUMBER, Example: 8.0020390000
WINDOWS_VERSION_NAME, Example: 'Windows 2000'
Edit: 2007-8-2
Changed SQL_SERVER_MAJOR_VERSION to varchar(40)
select
SQL_SERVER_MAJOR_VERSION =
convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
SQL_SERVER_VERSION =
convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
SQL_SERVER_PLATFORM =
convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
SQL_SERVER_EDITION =
convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
WINDOWS_VERSION =
convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
WINDOWS_BUILD =
convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
WINDOWS_SERVICE_PACK =
convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2)))
from
(
select
L1_BREAK_1 = charindex(' - ',L1),
L1_BREAK_2 = charindex(' (',L1),
L1_BREAK_3 = charindex(')',L1),
L4_BREAK_1 = charindex(' on Windows',L4),
L4_BREAK_2 = charindex(' (',L4),
L4_BREAK_3 = charindex(': ',L4),
L4_BREAK_4 = charindex(')',L4),
L1,
L4
from
(
select
L1 =
convert(varchar(100),
rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
) ,
L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))
from
(
select
zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(
select
yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
(
select VERSION = @@VERSION
) a ) a1 ) a2 ) a3 ) a4 ) a4
Results:
SQL_SERVER_MAJOR_VERSION SQL_SERVER_VERSION SQL_SERVER_PLATFORM SQL_SERVER_EDITION WINDOWS_VERSION WINDOWS_BUILD WINDOWS_SERVICE_PACK
------------------------ -------------------- -------------------- ------------------------------ -------------------- -------------------- ------------------------------
2000 8.00.2039 Intel X86 Standard Edition Windows NT 5.0 Build 2195 Service Pack 4
(1 row(s) affected)
drop view [dbo].[V_SQL_SERVER_VERSION]
go
create view [dbo].[V_SQL_SERVER_VERSION]
as
select
SERVER_NAME = @@servername,
SQL_SERVER_MAJOR_VERSION,
SQL_SERVER_VERSION,
SQL_SERVER_MAJOR_VERSION_NUMBER =
convert(int,floor(convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4)))),
SQL_SERVER_VERSION_NUMBER=
convert(numeric(20,10),(
convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4))*1000000+
convert(numeric(20,10),substring(SQL_SERVER_VERSION,6,30)))/1000000),
SQL_SERVER_PLATFORM,
SQL_SERVER_EDITION,
WINDOWS_VERSION_NAME =
convert(varchar(20),
case
when WINDOWS_VERSION = 'Windows NT 5.0'
then 'Windows 2000'
when WINDOWS_VERSION = 'Windows NT 5.1'
then 'Windows XP'
when WINDOWS_VERSION = 'Windows NT 5.2'
then 'Windows 2003'
else WINDOWS_VERSION
end),
WINDOWS_VERSION,
WINDOWS_BUILD,
WINDOWS_SERVICE_PACK
from
(
select
SQL_SERVER_MAJOR_VERSION =
convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
SQL_SERVER_VERSION =
convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
SQL_SERVER_PLATFORM =
convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
SQL_SERVER_EDITION =
convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
WINDOWS_VERSION =
convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
WINDOWS_BUILD =
convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
WINDOWS_SERVICE_PACK =
convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))),
VERSION = VERSION
from
(
select
VERSION,
L1_BREAK_1 = charindex(' - ',L1),
L1_BREAK_2 = charindex(' (',L1),
L1_BREAK_3 = charindex(')',L1),
L4_BREAK_1 = charindex(' on Windows',L4),
L4_BREAK_2 = charindex(' (',L4),
L4_BREAK_3 = charindex(': ',L4),
L4_BREAK_4 = charindex(')',L4),
L1,
L4
from
(
select
VERSION,
L1 =
convert(varchar(100),
rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
) ,
L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))
from
(
select
VERSION,
zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(
select
VERSION,
yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
VERSION,
xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
( select VERSION = @@version ) a ) a1 ) a2 ) a3 ) a4 ) a4 ) a5
go
grant select on [dbo].[V_SQL_SERVER_VERSION] to public
go
select * from [dbo].[V_SQL_SERVER_VERSION]
CODO ERGO SUM
View 16 Replies
View Related
Mar 9, 2007
Hi there,I am re-organizing the database. We used to have field 'names' in ourtable for our first name and last name. However, I want to have thosenames in different field.FYI, I have two different tables in different databases. The Adatabase contains A table with 'names" field. The B database containsB table with 'fname' and 'lname' I want to copy data A table to Btable.How can I parse names field into first name and last name fields?Here are some examples which are in the database.(id names01 John Doe02 John & Jane Doe03 Mr & Mrs Doe) something like this. It might contain '&' and two names. If thereare two names, then use first name.Thanks guys,
View 5 Replies
View Related
Sep 28, 2007
In my stored procedure, I want to parse @ArrayOfDays into @d1 through@d5.@ArrayOfDays is a varchar input parameter containing,for example, "1.7.21.25.60." - five elements.Most active vars:@i - loop counter@char - current char in string@tempVal - contains the current element as it is being built@tempValExecString - contains SELECT stmt for EXEC()I'm using EXEC() to execute a dynamically built SELECT.The error I get when calling from vb.net is:Must declare the variable '@tempVal'.Two manual traces indicate the logic is ok.I suspect my assignment statement for @tempValExecString.Any help would be appreciated. - BobC----------------------------------------------------------DECLARE@d1 varchar(3),@d2 varchar(3),@d3 varchar(3),@d4 varchar(3),@d5 varchar(3),@i int,@char char(1),@tempVal varchar(3),@tempValExecString varchar(30)SELECT @tempVal = ''SELECT @i = 1WHILE @i < LEN(@ArrayOfDays)BEGINSELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)WHILE @char <'.'BEGINSELECT @tempVal = @tempVal + @charSELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)IF @char = '.'BEGIN/* the following should produce "SELECT @d1 = 1" when it reads thefirst period(.) */SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '= @tempVal'EXEC(@tempValExecString)SELECT @tempVal = ''SELECT @i = @i + 1ENDSELECT @i = @i + 1ENDEND----------------------------------------------------------
View 13 Replies
View Related
Apr 4, 2008
Is there a way to parse a string to int? this is my example code. quote_id is a string i want to be treated as an int here.
Code Snippet
ALTER PROCEDURE [dbo].[GetNextQuoteID]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT MAX(quote_id)+1 as id From Quote
END
Thanks
K
View 3 Replies
View Related
Mar 14, 2006
Hi.
Does anyone come across this error message whenever u try to parse ANY SQL statements in Management Studio 2005 (even parsing SELECT GETDATE() fails)...
>>>
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
>>>
Thks!
View 1 Replies
View Related
Mar 5, 2007
I want to parse transaction log file.
Actually, i need to trace out changes in my database.[either by insertion/updation/deletion of data or adding/modifiying object.] within my application.
I study SQL Profile. But it is limited for its run and also resource hunger or extra burden on server in case of large size database and busy server.
So please guide me. Also suggest be its appropriate solution.
Thanks.
View 4 Replies
View Related
Feb 1, 2006
Can anyone tel lme why this does not parse in 2000 but works fine in 2005?
ALTER TABLE Segments ADD CONSTRAINT
PK_Segments PRIMARY KEY CLUSTERED
(
SegmentsUniqueID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
thanks
View 1 Replies
View Related
Apr 25, 2008
This is the type of data I have within my table and I need to take it from this to the example below
Types First Name Last Name
--------- ---------------- ----------------
6L4XX,6L5XX,6L8XX,6L9XX Bob Smith
6L4XX,6L5XX,6L8XX,6L9XX Dave Johnson
Types First Name Last Name
--------- ---------------- ---------------
6L4XX Bob Smith
6L5XX Bob Smith
6L8XX Bob Smith
6L9XX Bob Smith
6L4XX Dave Johnson
6L5XX Dave Johnson
6L8XX Dave Johnson
6L9XX Dave Johnson
I have to do this for MANY rows but I don't want to use a cursor. Should I be using Dynamic SQL? If so how should I go about starting out.
Thank You
View 16 Replies
View Related
Apr 5, 2006
i am executing an sql statement in sql server 2005 express edition. It says it is unable to parse the query then automatically re-arranges the sql text and then it is able to parse it and return me the result. my query is this: What am I doing wrong?
IF NOT EXISTS (SELECT MemberID, HotelID FROM tblHotels_Reviews WHERE MemberID = 3 AND HotelID = 3) BEGIN INSERT INTO tblHotels_Reviews(MemberID, HotelID, ReviewTitle, StayMonth, StayYear, Satisfaction, PricePerNight, ValueForMoney, CheckIn, FoodDining, BusinessFacilities, SafetySecurity, Rooms, StaffService, HotelFacilities, CheckOut, SuitableFor, Atmosphere, DoYouRecommend, Review, Anonymous, HasPhotos, ReviewDate, ReviewLanguage, PublishStatus)VALUES (6, 3, 'hello', 'January', 2002, 5, 234, 5, 5, 5, 5, 5, 5, 5, 5, 5, 'Singles,Couples', 0, 1, 'helloo', 0, 0, '06/04/2006', 'EN', 0) SELECT 1 AS RESULT END ELSE BEGIN SELECT 0 AS RESULT END
View 3 Replies
View Related
Jun 7, 2002
I create a Execute SQL Task and Load a stored procedure into the "SQL Statement" box. This stored procedure has many 'go' words and many commented lines either with '--' mark or '/*' or '*/' mark.
When Parse Query, get Package Error "Deferred prepare could not be completed. Statements could not be prepared".
But I could run the same proc under the isql/w with no problem.
How could I deal with this error? Though it looks the execution of the package still run successful. The Parsing just create uncertainty.
thanks
David
View 2 Replies
View Related
Mar 21, 2006
Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.
View 2 Replies
View Related
Aug 14, 2007
I am using MSSQL v8 (if that matters)
The data looks like the following
---------------------------
| PBP 20070420 2:26pm |
---------------------------
Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied.
I need to find all the dates that are within the last 10 months. How do I perform this task?
View 4 Replies
View Related
Feb 11, 2008
I am getting a pain in the butt error in DTS.
If I use and Execute SQL Task like this ...
------------------------------------
DECLARE @VAR
SET @VAR = ?
EXEC sp_Test @VAR
------------------------------------
and then click parse query I get an error "Syntax error or Access violation"
However; if I use it this way
------------------------------------
EXEC sp_Test ?
------------------------------------
it works.
Now here is the odd thing. If I put in a DECLARE statement and a global parameter "?" I get the syntax error.
But .... If I use a disconnected edit and use DECLARE and "?" then task will run properly. So the problem seems to be with the query parser.
The problem is I don't have the time to edit everything in a disconnected manner. Is there a way or update that will allow me to use DECLARE and "?". I have tried many hotfixes but none have worked.
View 2 Replies
View Related
Mar 31, 2008
This package will do very simple thing. It will parse the file and load it into table.
The format the file will be with fixed position:
From position 1 to 18, I need trim all the ending space and map it to a field of base table.
1. This is what I have done in the sql 2000 DTS package. How do I do the twist of data mapping on the SSIS?
I was using Active-x script in the 2000 DTS package.
EmployeeNumber = Trim(DTSSource(1))
EmployeeNumber = Left(EmployeeNumber, Len(EmployeeNumber) - 1)
2. At the Contrl Flow tab, I only have "Data Flow Task". I don't see the place I can do expression.
Thanks.
View 1 Replies
View Related
