DECLARE @UserName nvarchar(100)
SELECT @UserName = SYSTEM_USER
value 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
I use asp.net 2.0 to access a SQL Server Express. I want to record the user who modify the database, so I create a trigger as follows:
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for trigger here UPDATE Customer SET UpdateUser = SYSTEM_USER WHERE ID = (SELECT ID FROM inserted)END
In IIS ,I configed to use Integrated Windows authentication, The problem is when I modify the database, UpdateUser always setting to NT AUTHORITYNETWORK SERVICE.
How to record the user as the format DomainUsername?
Thanks,
Alvin
Trigger works great, except for one problem.
ALTER trigger [dbo].[Loans_UpdateTrigger] on [dbo].[List] for Updateas insert into Loan_Audit
select *, Getdate(), system_user, 'Update'
from Deleted
The system_user returns "DomainNameWEB$ instead of the actual users name.
Any way to get the actual users name???
thanks
Chuck Snyder
Excuse me, what is the difference between user and system_user and whichfunction should be used in audit trail table ?Thanks.
View 3 Replies View Related
Hi,
I need a DELETE trigger to log the user who actually deleted the item in Sql Server. Problem is that I am using connection pooling on my SqlClient side of things and so all users share the same connection string user id.
Now, I need to be able to access both the connection_user id (otherwise known as system_user) and also the user on the executing thread on the client. I do this in Oracle and DB2.
So, can you tell me how to pass this info into the connection and how to extract it within Sql Server.
ThxP
Hi,
We just restored our database from SQL Server 2000 to SQL Server 2005 for me to test things out. Right off the bat I started seeing errors where I could not select data from a table. I would get the "Invalid object" error unless I specified the schema of the object. I found this confusing because I was connected to the server using SQL Server Authentication as the schema_owner ( i.e user name and schema name were identical). I thought this was due to the fact that my DEFAULT_SCHEMA was not set but when I checked, user 'testuser' had schema 'testuser' as default_schema. I also made sure I was connected to the Database using SQL Server Authentication as user 'testuser'.
But the following would fail:
SELECT * FROM Employees
The following would however succeed.
SELECT * FROM TestUSer.Employees
I was really confused and when I read some posts and forums, I decided to check SYSTEM_USER and to my dismay, it returned my Domain login as the value as opposed to "TestUser". I re-connected using my Domain Account and SYSTEM_USER returned the Domaim Login as expected but when I switched to Sql Server Authentication and connected as user "testuser", SYSTEM_USER would still return my Domain Account. I believe this is the source of my problems. Can you tell me what's going on.?
I am a member of the sysadmins group and CURRENT_USER returns DBO. Please help as this is driving me nuts!
Thanks
I know that system_user can return different types of usernames based on the authentication method used to connect to the database. I am trying to nail down a standard field width for audit columns that store the return value from system_user but I can't find definitive information about the return type of the function. Does anyone know the maximum length of the return value from the system_user function and if its an nvarchar, varchar, nchar, etc.?
Thanks.
-- 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.
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 RelatedI 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
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!
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
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 RelatedIn 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 RelatedIs 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
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!
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.
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
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
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
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
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 RelatedI 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?
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.
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.
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.
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
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 RelatedHi 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
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.
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
Hi,
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them.
The format of the name field is 'AAAA-BBBBBB-123'
here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id.
I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID',
name AS 'name',
sequence AS 'num'
FROM
FirstTable A
INNER JOIN SecondTable q
ON (CONVERT(INT,RIGHT(name,3))= a.id)
INNER JOIN ThridTable t
ON(t.id = q.id)
INNER JOIN FourthTable s
ON (q.name = s.name )
WHERE A.id = @ID
AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1))
ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
Thanks,
Subha
I am learning how to parse XML data into SQL table. Below partial XML data contains multiple sections. I used OPENXML to parse one section but need to parse the remaining sections into a table. For example, below openxml code can only retrieve elements under "NewHires/Newhire/EmployeeInfo/PersonName" section. I also need to parse elements under "NewHires/Newhire/EmployeeInfo/ContactMethod" sections. Notice that there are three subsections underneath the ContactMethod section ("/Telephone", "/InternatEmailAddress", and "/PostalAddress") sections. Not to mention there are EmergencyContact section follow behind.
OpenXML can only extract one section at a time. Does it mean I have to write multiple OpenXML, store them into multiple temp tables then merge them into a single table? If that is the case, how to save all these data into a single table?
parse all the elements in lieu of OpenXML?
Here is the partial XML data:
<?xml version="1.0" encoding="UTF-8"?>