I have a project in which I have about 20,000 records in sql database table.
What I would like to do is generate a query that lists all the unique words in a particular field acros the entire table so as to generate a glossary of words.
if we had a table that looked like
ID Description
001 This is the first record
002 This is the second record
003 This is not the first record
and the query was run on the description field, then the result I would like to see is
This
is
the
first
second
not
I hope this makes sense. Any help is appreciated.
My MASTER table that contains text is very simple: TEXT_ID INT TEXT varchar
I was thinking of something like creating another table to keep track of selection criteria: USER_NAME varchar // keeps track of which user made the selection TEXT_ID INT // points to the text id in the MASTER table START_POS INT // start position of the selection END_POS INT // end position of the selection COLOR INT // color of the selection
The thing I don't like about this method, is if user(s) have many words higlighted in the sentences, this table may get very large.
I also thought of maybe combining START_POS, END_POS & COLOR into comma delimited entries:
EX: 1:5:240, 25:30:125 // START_POS:END_POS:COLOR
The thing I don't like about this method, the parsing calculations may take a while to execute.
My goal is to stream as much text data as possible and make it as fast as possible. How does Google or any one else do it?
P.S. Having a copy of a MASTER table with HTML tags in TEXT field is not an option.
In table1, column1 I am trying to split any string that has a pace in the middle like 'ABC XYZ' to two columns, Column2 to have 'ABC' and Column3 to have 'XYZ'
HI if I have a recrod in a plain text file , and it has some colums information including FromDate and ThruDates if the date range is over 30 days, Then the record should split and becomes 2 records rather then one.
for example:
if i have FromDate 07/01/06 and ThruDate = 08/25/06
than that single record should be saved as two records
like
Record#1 FromDate ThruDate
1 07/01/06 07/30/06 2 08/31/06 08/25/06
so what should I do to work it out, in the database, what could be the t sql or trigger that I have to use??? help please.
I'm trying to write a scrip to compate two columns in two different tables to each other (billing_payee to Debtor_name)
The problem i have is that the info in the billing_payee column is CLIENT RE DEBTOR INITAL SURN and the infor in Debtor_name column is [i]DEBTOR TITLE NAME SURN[i]
What i need to be able to do is select all from Billing_Payee table where the Billing payee [i]DEBTOR INIAL SURN[i] is not the same as the info in Debtor_name.
I somehow need to split out the inial - first letter of debtor name and surname.
If i can't do it this way i need some other way of comaparing the two.
I am fairly new to t-SQL however have a pretty good grasp of it.
Please let me know if there is anything else i can provide to make this any easier any help is much appreciated!!
I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows. My table structure is: create table #scoresheet (Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255), Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255), Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255)); I would like to have three rows with four columns. What I've done so far is create a stored procedure that uses a table variable: create procedure sp_splitsinglerow as
declare @Scoresheet_rows_table_var table ( ReviewArea varchar(25), NumericalValue int, VI varchar(10), Comments nvarchar(255)); insert into @Scoresheet_rows_table_var (ReviewArea, NumericalValue, VI, Comments) select Decisions, DNumericalValue, DVI, DComments from #scoresheet
The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.
Any ideas on how to achieve that?
BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)
GreetingsFor performance reasons I am trying to determine how often the pages inthe index split due to inserts. Are there any records in the 'sys' tables,etc. Thanx
For some reason I am having a really hard time grasping IS and I have a task that I would imagine is easy.
I have a flat file source with 6 columns, I would like to import this file into two flat files. One file containing columns 1,2,3,5 and the second containing 2,4,5,6. I created the connection managers for both destination files, but I can€™t determine what transformation tool I need to accomplish this task? Could you help?
So, I need to delete the "timestamps" the an user has done, which the difference between one stamping and the following one is less than 2 minutes for the inputs for entrances and I need to remove it when the "stampings" which the difference between an one stamping and the previous one is less than 2 minutes for exits.
Can you help me please? I will appreciate a lot your help.
Violation of PRIMARY KEY constraint 'PK_VTA020SAP2'. Cannot insert duplicate key in object 'VTA020SAP2'. The statement has been terminated.
INSERT INTO VTA020SAP2 SELECT VTA020SAP1.*, NULL as xCLIPRO, NULL AS xPAIS, NULL AS xCONFREI, NULL AS xCONCPER, NULL AS xMONEDA, NULL AS xFAX, NULL AS xTIPEXP, NULL AS xCASILLA, NULL AS xDIAENTR, NULL AS xPUNVEN, NULL AS xCIUDAD, NULL AS xREGION, NULL AS xCONDIC, NULL AS xINSPAG, NULL AS xCUMPLE, NULL AS xPERVIS, NULL AS ESTADO FROM dbo.VTA020SAP1 INNER JOIN VTA020 ON VTA020SAP1.CNRCLI = VTA020.CNRCLI AND VTA020SAP1.CNRCLI IN (SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1 GROUP BY VTA020SAP1.CNUECOD) AND (not EXISTS (SELECT VTA020SAP2.* FROM dbo.VTA020SAP2 INNER JOIN VTA020SAP1 ON VTA020SAP1.CNUECOD = VTA020SAP2.CNUECOD WHERE dbo.VTA020SAP2.CNUECOD = dbo.VTA020SAP1.CNUECOD))
If I want to change the value of a special bit in a column, how to wirte the SQL sentence in sqlserver. I mean in a column, the value is 'test', then how to change the 's' to 'e' or any other value by just one SQL sentence. Thanks.
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Hi, all friends. I'm using SQL-SERVER 2000.I have a table like this:NIF Name----------------------------------------A-1234 Company1B-123-B1 Company2C-4568 Company4D-453-DF Company5I want delete the symbol "-" in the row "NIF". eg. A-1234 change toA1234, D-453-DF to D453DFSo how can I write the sentence . Should I use the Stored Procedure?Thank you very much!!Simon
i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:
INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')
with empty values, but then it doent update in the dataBase-only if all the values appear- what the solution of it? Thanks
I have two tables. A table called users (content speaks by it self) and a table called groups. Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt. The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId. Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table. Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command. Thanks in advance, Greetings from Esben
I would like to know how can I change a "not in" clause with the same results in a SQL sentence in order to improve the performance of my SQL sentence.
I want to add a parameter to the following SQL-sequence. ActivityGroup is the table and I want to select the current activityGroupID in place of XXXXX. Do I need to put this in a stored procedure?
select * from ActivityGroup where cpgActivityGroupID = XXXXX and cgpRegistredFrom > getDate() and cgpRegistredTo < getDate()";
I have to parse a sentence into separate words. This has to be done in a Stored Proc.
If the sentence is: "The sun is rising". Then I want to store the words "The" "sun" "is" "rising" into 4 separate variables. (The separator is a space).
Has someone already written something like this before? I do not want to re-invent the wheel.
CREATE TABLE [dbo].[instructions]( [site_no] [int] NOT NULL, [instructions] [text] NULL ) Select top 3 * from instructions
Output
Site_no Instructions 20 Request PIN#510 then proceed 21 Request PIN#987 if wrong request name 22 Request PIN#688 allowed to use only numbers
All text instructions start with “Request PIN#XXX†also after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN#XXX†the text “and codeword†keeping the current rest of text
How can I set e REPLACE CAST sentence using something LIKE PIN%%%%
To get these type of results
site_no instructions ----------- ---------------------------------------------------- 20 Request PIN#510 and codeword then proceed 21 Request PIN#987 and codeword if wrong request name 22 Request PIN#688 and codeword allowed to use only numbers
the talbe row like this: ID Name Scoe 11 Tome 20 12 Jack 30 11 Tome 40 12 Jack 10 13 John 10 My query command like this: Select T1.Id,T1.Name,T2.mathfrom st T1right join(Select Id as Id2,Sum(Math) as Math from St group by id) T2on T1.id=t2.id2where t1.id = t2.id2 While the reuslt is : Id Name Score 11 Tom 60 11 Tom 60 12 Jake 40 12 Jack 40 13 John 10
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows, and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same. but why ? please help me !
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder @mywhere VARCHAR(100) AS BEGIN SET NOCOUNT ON; select * from order @mywhere END
Hi everyone!I am working with Delphi v7 and MS SQLServer.I am trying to insert data in a table with a SQL sentence. Some of thefields of my table are type char or varchar, and they can have nullvalues.¿What do i have to write in the SQL sentence to insert a null value inthose fields?I tried with '', an empty String, but it doesnt work, the tablesstores an empty String (logical :-)).In the SQLServer GUI you have to press CTRL + 0 to insert a NULLvalue, but how can i tell this to the SQLServer through a SQLSentence?Well, thank you very much.
I was recently told SSRS does not support the ability to underline a portion of text in a sentence. Does anyone know if this is true? Couldn't "text decoration" "underline" and "span" be used to underline the web address in the sample sentence below?
I have a illegal keyword table name as "Illegal_keyword_Master" where near about 2000 illegal keyword are stored... Now I have a big Sentence(bunch of words)...
If any illegal is keywords is found from "Illegal_keyword_Master" table in that sentence then the sentence is "banned" otherwise the sentence is "OK"....
I have an very long ntext field, made up of many sentences that I append a full stop to every one, I also strip out any line breaks within the text. However I get this error, when I look it up it comes up with "Failed to locate the ending boundary of a sentence."
Hello i need to know hoy to use the LIKE operator to find results that contains 2 or more words. ================TABLE EXAMPLE====================== I HAVE A TABLE CALLED ITEMS
ITEMNAME Good Bike Good Mountain Bike Klein Bike Mountain ===================================================
If i use SELECT ITEMNAME FROM ITEMS WHERE ITEMNAME LIKE '%Good Bike%' i only get: Good Bike
What to code i need to write if i want to get that results for QUERY: "Good Bike" returns Good Bike Good Mountain Bike
Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc. For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"') If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"') I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites. Is there something I am not doing properly?
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
Hi There, I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page: Execution of a full-text operation failed. A clause of the query contained only ignored words Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words. In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far. Many thanks Stuart