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 have just inherited a new project consisting of data imported into sql 2005 from a multi-dimensional database. After finding the correct ODBC and importing the data I believed that I was done, but after reviewing the resulting structure I discovered why this was called a €œmulti-dimensional€? database. The resulting imported data is completely de-normalized and resembles an excel spreadsheet more than a relational database. An example of this structure is the persons table. The table has multiple columns, some of which contain the multi-dimensional fields. These fields contain multiple values which are separated with a tilde, €œ~€?. I need to parse out the data and normalize it. In the specific sample of data below I attempting to take the personid, associates, and assocattrib and insert them into a sql table, associates. This table references the persons table where the personid and the associates references the personid in the persons table.
CREATE TABLE [dbo].[associates]( [associd] [int] NOT NULL REFERENCES persons(personid), [namepkey] [int] NOT NULL REFERENCES persons(personid), [assocattribute] [varchar](20) NULL )
The purpose of normalizing this data will be to show the realationship(s) between people as it has been documented in the previous data structure, i.e. person 1 is an associate of person 336 and the attribute is WIT.
My problem lies in attempting to parse out the associates and assocattrib columns and relate them to the appropriate personid. The personid relates to each associate and assocattrib and the tilde, ~, separates the values ordinal position which, in sql, would be separate rows. For example the single row: personid associates assocattrib 58201 252427~252427~252427 VICT/SUSP~WIT~RP Should be: 58201 252427 VICT/SUSP 58201 252427 WIT 58201 252427 RP
The imported data can have no associates: personid associates assocattrib 152683 NULL NULL
or up to 69 associates, I am not even going to try to paste all of that here.
There are over 400,000 rows that I am looking at parsing, so any way of doing this in t-sql or the clr would be GREAT. This data is stored in SQL 2005 standard SP2. The specific machine is our test/reporting server, so I am not necessarily concerned with the best performing solution, I am leaning more towards providing some free time for me.
Any help or suggestions, including better ideas on the table structure, would be greatly appreciated.
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData 1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.] 2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.] 3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set) ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8 1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt 2 [abc1.Gray...abc8.] Gray Shoes Striped jacket 3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like: DECLARE @string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]', @searchString1 varchar(20) = 'abc1', @searchString2 varchar(20) = 'abc2';
SELECT newstring FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
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.
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd"> <Asset_Collection> <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into: Table: TABLE_A
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl( invoicenum int, salesreplist [text] NULL, reprate1 int NULL, reprate2 int NULL, reprate3 int NULL, reprate4 int NULL, reprate5 int NULL, )
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
We are using a table that may give 1 to and unknown number of data elements (ie. years) .  How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.   car make year1 year2 year3 A  volare 1995 1996 1997 a  volare 1997  1998  1999 b toyat  1965   1966  1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
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
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.
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)
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
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,
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----------------------------------------------------------
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
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. >>>
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.
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.
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.
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.
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'
--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
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
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
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!
