I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV
So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......
Any way to get this count using MS SQL?
Hi everybody,
is it possible using SQL Server search for a word on each
table on each field?
Thanks
Fabio
I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.
View 9 Replies View RelatedWe're migrating from Access to MS SQL server 7.0
Inside the edit of an Access tables; the Description column can also be used to place comments other then the description of the field,
ie. explanation of field's use
pay payrate per hour in USD
or allowed values
status O=open, C=closed
I can not find something similar inside SQL 7.0.
If this option is not available, what other alternatives are there ?
Patrick
PS. what is the CTEXT field inside the SYSCOMMENTS table ?
Newbie question:Aside from the single quote (i.e. chr(39)) what other characters can causeMS-SQLserver to cough-up the insertion / update back in your face?TIA
View 1 Replies View RelatedHi,
I am using SQL Server 2005 Express. I am creating a table, which will have a comments field. Whats an appropriate data type that should be used for a "comments" field? So far, I have unlimited text and characters can be entered, but that a limitation can be placed.
Thanks
Chirs
I have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
Hi,
I was hoping somone could help. I need to count the number of words in a specfic column in a table if i didn't know what the words said.
I can count the number of words using
DECLARE @String VARCHAR(4000)
SET @String = ' words to be counted'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1
AS Results
but this requires me to know what the words are and not count them from the column.
I have one table tbl_resume.I have store all resume in one column ...RESUME...i have to count Number of words(Skills) used in resume :- JAVA or C++
View 5 Replies View RelatedHow can I count the rows when I make new table. Here is my code:
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID, COUNT(TO_CHAR(HIRE_DATE, 'DAY')) AS NUM_EMPLOYEES
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Monday'
GROUP BY EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID;
Here is the table: [URL] ....
I have 10 rows, so I would like under NUM_EMPLOYEES to be displayed 10 on each row.
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedI have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.
Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.
Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.
So the end result would look something like this:
Feed------No of Records
A----------4 (from TableA)
B----------7 (from TableB)
C----------8 (from TableC)
D----------1 (from TableD)
Possible?
If you use the LEFT() function for example it provides intellisense support for some of the required parameters.
Is there a way to get that same descriptive text in user defined functions?
Some comment block you define when creating the function?
Hi everyone. I've taken a while off of developing site in ASP.net but had a site that I wanted to upgrade a little, but needed a little help.What I have currently is a website of a person with videos and images of that person. To view the videos, I have a "view_video.aspx?ID=" page that plays the video from YouTube by looking my database for the ID, YouTube URL, Name, and Description. What I want to do is create a "comments" table for visitors to add comments for each video and then display all comments on the page.So far, my "comments" table looks like this. ID, otherID, name, email, comment, type, date.The ID is the id of the comment, otherID is the foreign key to the "videos" table, the name is the name of the person leaving the comment, e-mail is for the person, comment is the text, type is the type of video (tutorial, sampler, random video), and date is a timestamp for when users leave the comment.So hopefully I have a good start. I don't have any code to show right at the moment for I am at work, but if anyone has any ideas or critiques so far, I'd love to hear them. This is an interesting project for my friend and I'd love to implement this sometime.Thanks,TetrisSmalls
View 1 Replies View RelatedI am working on a project that lets visitors to my webpage post comments. I have there name, city, etc. stored into an sql database. This all works until I put text into the comments field of my form. I get this message when it is trying execute -- String or binary data would be truncated. I was wondering what data type to use, I have used char and varchar and neither one is working. Is there anything else I could try to fix this.
View 1 Replies View RelatedHas anyone discovered how to store table and column comments inside SQL Server 2000? It was supposed to be an added feature with 2000.
thanks!
Hi,
I was wondering if someone can help me with is problem.
I have uploaded word docs to the db which is fine. The problem is viewing. I can view then as word documents but the boss does not want the files opened in word.
Is it possable to retrieve the file from from the db and put into say a textbox or lable.
I can see the letter P using this code
Dim Doc() As Byte = New Byte(Convert.ToInt32(0)) {}
Dim bytesReceived As Long = DBContent.GetBytes(0, 0, Doc, 0, Doc.Length)
Dim encoding As ASCIIEncoding = New ASCIIEncoding
lblTest.Text = encoding.GetString(Doc, 0, Convert.ToInt32(bytesReceived)).ToString
Can any help
Thanks
I have a text field that contains multiple words. Is there a way with SQL to relace a single word with another?
for example:
FIELD : CompanyName
DATA : Microsoft Corp.
DATA : IBM Corp.
etc...
DATA : Canon Corp. of America
How can I run a routine to just REPLACE "Corp." with "Corporation" ?
Thanks for your help!
Scott
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
View 9 Replies View RelatedI am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
Is there a way to add comments on a table in sql server?
View 14 Replies View RelatedI have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
Any thoughts, words of warning?
Thanks
Mark
In SQL SErver 2008, I have a text column. I need to display either 2nd word in the text column or 1st word in the text column based on certain conditions.
How shall i display either 2nd word or 1st word from a text field.
Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter
declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1
pk occurrences
----------- -----------
2 2
pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.
I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
I have a detail table of comments. I need to grab the fist 50 characters from the comment field from this table. The kick is that I need to get the comments from all of the records in the detail table, as the comments might be less than 50 characters, so i need to pull the comments from the other comment records that apply to the master table record.
right now i have the comments in a join to the master table, but this is causing a few records to be returned....
I guess what i am looking for is a way to grab the comment value from the first record of the detail table.
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
In company there are two version of app. New one save order status as "Ordered" the old save as "ORDERED". In order to find out who use old version, how to code in where clause like below?
select userid from order where status = 'ORDERED' (only capital)
(Above code will list all)
How to update column with multiline comments.
eg. I need to update Notification column in my table as below.
Exit Formalities Notification sent to:
Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress