Parse A Field
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
ADVERTISEMENT
Dec 7, 2007
Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?
View 14 Replies
View Related
Jul 23, 2005
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
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
Jun 27, 2007
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
View 3 Replies
View Related
Sep 18, 2015
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5.
Desired data format: Name1 Name2 Name3 Name4 Name5.
Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally
Bush/Jim/Sr/Etal/Trustee
View 43 Replies
View Related
Apr 9, 2008
I have a third party application with a ntext field that I need to parse the data out of. The data looks like this:
<xmlF><FNumber type="int">2421</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate</xmlF>
The fieldname is Data and the tablename is ProcessData
Again, this looks like xml, but the field type is ntext. I would like to create a view displaying the parsed data in fields. How would I go about parsing the data?
Thanks.
View 12 Replies
View Related
Jan 3, 2008
IE:
ID ContactID
1 4, 5, 6, 8
2 3,4,6
Someone coded their database like this. It is a SQL server table with these two fields.
How do I use SSIS to parse out that single field?
View 5 Replies
View Related
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
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 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
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. I was using Active-x script. How do I do parsing when I map the field to the table?
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
Oct 16, 2005
Please let me know if you come across any name strings this function cannot parse.
CREATE function FormatName(@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName parses a NameString into its component parts and returns it in a requested format.
--
--@NameString is the raw value to be parsed.
--@NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
--[H] = Full honorific
--[h] = Abbreviated honorific
--[F] = First name
--[f] = First initial
--[M] = Middle name
--[m] = Middle initial
--[L] = Last name
--[l] = Last initial
--[S] = Full suffix
--[s] = Abbreviated suffix
--[.] = Period
--[,] = Comma
--[ ] = Space
--Example: select dbo.Formatname('Reverend Gregory Robert Von Finzer Junior', 'L, h. F m. s.')
--Result: 'Von Finzer, Rev. Gregory R. Jr.'
--Test variables
-- declare@NameString varchar(50)
-- declare@NameFormat varchar(20)
-- set@NameFormat = 'L, h. F m. s.'
-- set@NameString = 'Reverend Gregory Robert Von Finzer Junior'
Declare@Honorific varchar(20)
Declare@FirstName varchar(20)
Declare@MiddleName varchar(30)
Declare@LastName varchar(30)
Declare@Suffix varchar(20)
Declare@TempString varchar(100)
Declare@IgnorePeriod char(1)
--Prepare the string
--Make sure each period is followed by a space character.
set@NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
--If the lastname is listed first, strip it off.
set@TempString = rtrim(left(@NameString, charindex(' ', @NameString)))
if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))
ifright(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)
iflen(@LastName) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get rid of any remaining commas
while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')
--Get Honorific and strip it out of the string
set@TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
if@TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'CPL', 'Corporal', 'SGT', 'Sergeant', 'GEN', 'General', 'CMD', 'Commander', 'CPT', 'CAPT', 'Captain', 'MAJ', 'Major', 'PVT', 'Private', 'LT', 'Lieutenant', 'FATHER', 'SISTER') set @Honorific = @TempString
iflen(@Honorific) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get Suffix and strip it out of the string
set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if@TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString
iflen(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
if @LastName is null
begin
--Get LastName and strip it out of the string
set@LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
set@NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))
--Check to see if the last name has two parts
set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @LastName = @TempString + ' ' + @LastName
set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
end
end
--Get FirstName and strip it out of the string
set@FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
set@NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))
--Anything remaining is MiddleName
set@MiddleName = @NameString
--Create the output string
set@TempString = ''
while len(@NameFormat) > 0
begin
if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.'
begin
set @IgnorePeriod = 'F'
set @TempString = @TempString +
case ascii(left(@NameFormat, 1))
when '72' then case @Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
when 'Lt' then 'Lieutenant'
when 'Capt' then 'Captain'
when 'Cpt' then 'Captain'
when 'Cmd' then 'Commander'
when 'Gen' then 'General'
when 'Sgt' then 'Sergeant'
when 'Cpl' then 'Corporal'
else isnull(@Honorific, '')
end
when '70' then isnull(@FirstName, '')
when '77' then isnull(@MiddleName, '')
when '76' then isnull(@LastName, '')
when '83' then case @Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@Suffix, '')
end
when '104' then case @Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
when 'Lieutenant' then 'Lt'
when 'Captain' then 'Capt'
when 'Cpt' then 'Capt'
when 'Commander' then 'Cmd'
when 'General' then 'Gen'
when 'Sergeant' then 'Sgt'
when 'Corporal' then 'Cpl'
else isnull(@Honorific, '')
end
when '102' then isnull(left(@FirstName, 1), '')
when '109' then isnull(left(@MiddleName, 1), '')
when '108' then isnull(left(@LastName, 1), '')
when '115' then case @Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@Suffix, '')
end
when '46' then case right(@TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III')))
set @IgnorePeriod = 'T'
end
set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
end
Return @TempString
end
View 20 Replies
View Related
Jul 23, 2005
In MS Access 2000 if I have a String such as:Column1Delta CC: 123Charley CC: 234Foxtrot CC: 890and I wanted to extact just the numbers in to a field called CCI could use this formula in a calculated field:CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)resulting in:CC123234890Any 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 ofproblems.Any help appreciated!RBollinger
View 2 Replies
View Related
Nov 10, 2005
DECLARE @UserName nvarchar(100)SELECT @UserName = SYSTEM_USERvalue returned is "DomainNTSignonName"What I want is only "NTSignonName"Is there a function to do this or an easy parse for this in SQL2000?lq
View 2 Replies
View Related
Jul 20, 2005
Hi all,I have a table of text and associated data. I want to break apart the textinto individual words, yet retain the data in other columns. For example:Sentence: Chapter:--------------------------I like cats. 1Joe likes dogs. 2Should become:Word: Chapter:--------------------------I 1like 1cats 1Joe 2likes 2dogs. 2Are there built-in SQL parsing functions, If not, what text handlingfeatures would be most useful for building them?Thanks!
View 7 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. I was using Active-x script:
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 3 Replies
View Related
Mar 13, 2008
Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....
Here is the sample string: MFD-2fdr4.zip
CREATE FUNCTION Parse(String, '-' , '.')
.....
....
.....
END
and it shoud return 2fdr4
View 8 Replies
View Related
