How To Parse Field LastName,FirstName

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


ADVERTISEMENT

Transact SQL :: To Get FirstName And LastName From One Word

Aug 25, 2015

How to retrieve data as a two names from one columns.

For example: My column has naming like this  Shannon.Cooley, I want to display like this:

FirstName         LastName                           FullName
Shannon         Cooley                           
Shannon  Cooley

How to write the query to get the above table?

View 7 Replies View Related

An Interesting Lastname, Firstname, Middlename Challenge

Sep 6, 2006

I'm sure you all have seen situations where a field has a combined namein the format of "Lastname, Firstname Middlename" and I've seenexamples of parsing that type of field. But what happens when the datawithin this type of field is inconsistent? Here are some examplesApple, John A.Berry John B.CherryJohn CDonald John DHow does one parse the data when the data isn't consistent?

View 2 Replies View Related

Please Help: Needed T-SQL Query To Get The MIN(LastName)+' - '+MAX(LastName) For Each LastName Initial In The Db

Jan 13, 2008

My users wanted to view all the "FIRST and LAST" Lastname in each of the LastName initial in the database.
I have been trying to join/connect/subquery/merge/etc the following two set of codes but without good results.

Fist, i get all the lastname initials:
SELECT DISTINCT LEFT(LastName, 1) AS LastNameInitial
FROM Persons.Persons
ORDER BY LastNameInitial

Results to:
--------------------------------------
A
B
C
D
...
Y
Z


Then, i want to get all the FIRST and LAST LastName for each LastNameInitial
I only have this partial idea (can't get the rest):
--------------------------------------
SELECT MIN(LastName) + ' - ' + MAX(LastName) AS LastNameGroup
FROM Persons.Persons
WHERE (LastName LIKE 'A' + '%')


Only results to:
--------------------------------------
Abad - Ayun


MY USERS WANTED TO HAVE THIS RESULT (In a single query):
------------------------------------------
Abad - Ayun
Babaran - Buted
Cabaccan - Curugan
Dacquil - Durwin
Eda - Evangelista
...
Yadao - Yumul
Zalun - Zunajo



Thanks in advance for your help.
God bless!

View 3 Replies View Related

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 View Related

Parse A Numeric String From A Field

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

Parse XML Strings In SQL Text Field

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

Parse Field Into Multiple Rows

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

Transact SQL :: Parse Name Field Based On A Delimiter

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

How Do I Parse Data Stored In Ntext Field

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

How Do You Parse A Single Field List Of Values Separated By Comma?

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

How Can I Parse Text Held In MS SQL 2005 Text Field

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

Mutator 'FirstName' On '@test' Cannot Be Called On A Null Value.

Apr 11, 2007

I'm having trouble figuring out how to use a UDT class. I'm simply trying to test out the class in a query window in the management studio. Here's the simple snippet:



declare @test TestObject;

set @test.FirstName = 'bob'

select @test.FirstName



Obviously my object has a string property called FirstName. When I execute this simple query I get the following error:



Mutator 'FirstName' on '@test' cannot be called on a null value.





Any ideas?

View 1 Replies View Related

Help With TSQL To Combine Lastname And First Initial From Full Name

Aug 16, 2007

Hello fellows,

I am trying to write a query, here is the detail:

I have a table of users. Table schema:
FULL NAME, LOGIN

example data:

Anderman John, AndermaJ
Williams Robin, WilliamR
Foot Jay, FootJ

The LOGIN field will be my resulting field after I run my query.

It has a "FULL NAME" column, and a "LOGIN" column. I want to extract the first 7 characters of LAST Name from the FULL NAME field and extract the First Name Initial(just first letter of the first name) from the FULL NAME field and then set the LOGIN field with the resulting combination. The FULL NAME column is in the order of LastName FirstName.


Here is what I have been trying to write:

Declare @spacePos as int

Select @spacePos = charindex(' ', fullname) From TIUSER

Update TIUSER

SEt LOGIN = ltrim(Substring(fullname, 1, 7)) + Substring(fullname,@spacePos+1,1),

where @spacePos <> 0 and LOGIN = ' '


Currently we only have last and first names in our database and we will keep it that way since we don't store middle names and hopefully that will not be an issue.

But it seems that my solution was not working since if the last name is less than 7 characters then it should only grab upto the SPACE and not beyond that , but it was grabbing beyond the SPACE so how do you fix that?

That is I want atmost 7 characters from LAST NAME but incase of less than 7 then upto the space found.


Any help would be highly appreciated.

View 5 Replies View Related

What's The Equivalen Function Of StrConv ([FirstName] , 3 ) In ACCESS In Sql Server 2000?

Jul 20, 2005

HI,i got a problem while using StrConv function in sql server.My requirement is:-suppose in a name field i have "jhon smith" Now i want to run a sqlwhich will give me the result like "Jhon Smith"--which means uppercase first.Now i am running a query like below which is giving me "Jhon smith"but i want "Jhon Smith".SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,len(firstname)-1)) AS [FirstName_] from my_table;But the above query is not giving me the right result. Pleasesuggest...ThanksHoque

View 2 Replies View Related

Parse Xml Into Sql?

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

How To Parse A Name

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

Parse IP Address

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

Parse @@VERSION

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

Parse Fields

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

I Want To Parse @ArrayOfDays Into @d1 Through @d5

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

Parse Nvarchar To Int?

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

Parse SQL Statements

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

Parse SQL Transaction Log In .NET

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

Parse Error

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

How To Parse Out Data

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

[name] &#043; &" &" &#043; Lastname

Jan 27, 2007

I have 2 data flows tasks, salesperson and customer, where I want to concatenate name and Lastname into Name, using a Derived Column...
[name] + " " + Lastname

In the first one, Destination Preview show NULL on every Name row. In the other the names are joined as "DanWinter". Why don't the concatenate according to expression?

View 4 Replies View Related

Unable To Parse Query

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

DTS Parse Query Error...

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

Parse A Column Containing SYntax

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

Parse Data And Then Compare

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

SQL 2000 DTS. Parse Query Bug?

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

Parse Flat File

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







Copyrights 2005-15 www.BigResource.com, All rights reserved