Hi all,
Is there a function in SQL that will return the number of occurence of 1 string to the other.
e.g.
string1 = "1"
string2 = "2131145"
function should return 3
How do i obtain this in SQL?
Thanks in advance
Hi ...I have a weblog database where I want to count the occurences of atable of string values that appear in all the urls viewed.My tblWebLog as a field that contains the url ...tblWebLog.[cs-uri-stem]I have another table ... tblStrings ... that has a field [strSearch]for a string value and an integer field [intViewCount] to count theoccurence of the string in tblWebLog.[cs-uri-stem]I've been trying ...Update tblStringsSet [intViewCount] = (Select Count(*) From tblWebLog Where[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%').... but it doesn't fly and I'm stumped. Any thoughts?Cheers.
View 2 Replies View Related
Hi All,
I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph.
Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working?
Thanks a lot in advance and let me know if the question is not clear.
Manoj Deshpande.
Hi Friends:
I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.
For example, I run the following queries in oracle to get the desire result.
SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;
INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2
SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;
INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4
SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;
INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6
SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;
INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10
Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?
--Raj
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record.
It would be a great help to me.
Thanks,
John
Hi all,How can I get a row that has only one occurence in a table? Notthrough 'distinct' because this gets a single row that might havemultiple occurences, I want to get only rows that have only oneoccurence in the table.I tried the group by and within the having clause I placed thecount(myfield) = 1, did not work...select f1, f2, count(f2)from table1inner join table2 on table1.id = table2.fidwhere .....group by f1, f2having count(f2) = 1gives me result like this: (basically in this situation, I dont want toreturn any results)f1 f2 count(f1)--- --- -----------1 a 12 a 1any ideas sql gurus?thanks
View 3 Replies View RelatedI have a table that tracks GPS records broadly speaking as followstsDATETIMEusernameVARCHAR(16)...GPS data...I want to select the most recent GPS data for each distinct user.That is, the table will have may records for a given username, but Ionly want the most recent for each one.For a single user I know I can doSELECT TOP 1 * from <table> order by ts descBut I want a set of results that effectively does this forall users, and I can't work out the necessary query/subquery Ishould be using.I'm sure I'm missing something fairly obvious, so usual newbiedisclaimers apply.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 4 Replies View RelatedWe have a strange occurrence in one of our dev Sql server. Our platform is Sql Server 2000 on SP4. Build Number is 8.00.2039
TableA
Sid int, --Non-Clustered Index
Aid int,
Uid int,
€¦..
TableB
Iid int,
Sid int, ---No foreign key constraint to TableA.Sid but logically refers to that column. --Non-Clustered Index
€¦..
Now, we are working on schema changes, now TableB looks like
TableB
Iid int,
Uid int, ---Logically refers to TableA.Uid
€¦..
Sid in TableB is dropped and a new column Uid is added.
And there is no Sid in TableB.
We have an existing SP that refers to TableB.Sid like
Create proc sp_ab
As
Select Uid from TableA where Sid = (Select Sid from TableB where Iid = @Iid)
After making the schema changes listed above, if I try to compile this SP without making any changes to the procedure, it should fail. Surprisingly it passes compilation and also retrieves data using hash joins and doing table scans on TableA avoiding index seeks. This SP used to take less than second, now takes almost 90 seconds to retrieve data. I couldn't replicate this problem with another set of objects.
If I make changes to the SP sp_ab like
Create proc sp_ab
As
Select Uid from TableA where Uid = (Select Uid from TableB where Iid = @Iid), it runs less than a second as it should.
Whats surprising is, if I do 'Select Sid from TableB where Iid = 1 ' in Query analyzer, it throws error referring to invalid column Sid in TableB but couldn't throw this error when the sql is wrapped in a SP. Everything else in the DB and in the db server seems to be ok except this.
If I query Syscolumns table, I couldn't see any references to Sid in TableB after the schema chnages and not sure why the code in SP is not caught during compilation. Any clues.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies View RelatedI have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies View RelatedI have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash
View 1 Replies View RelatedHi, everyone
There is a table t1 with two fields, such as,
ID NAME
１ Ｔｏｋｙｏ
2 Xian
3 America
For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.
Thank you very much!
In SQL 2000 DTS, I was able to append data from an ODBC source to a SQL 2000 destination table. The destination table was created by copying an attached source table in Access to a new table, then upsizing it to SQL. The character fields come over as varchar, and that seemed to be fine with the DTS job.
Now using the same source table and the same SQL destination, only in SQL 2005 with Integration Services instead of DTS, I get an error because the connection manager interprets the source text fields as Unicode and the destination fields are varchar.
I could script the table and change the text fields in the destination table to nvarchar, but this could have adverse affect on the application that uses the destination table. Is there a way to make the connection manager see the source text fields as varchar, or have the integration package allow the append even though the destination is varchar and the source is nvarchar?
I'm trying to figure out how to convert an INT so that I can add it to another string in a statement likeSELECT '<a href="http://mydomain.com/' + [IntValue] + '">' + [LinkName]Where [IntValue] is an INT field containing a folder number. I tried: TO_CHAR([IntValue, '999')but it didn't work. And I tried to find a TO_STRING function, but had no luck.Any advice to someone who is savy, but new to SQL Queries?
View 2 Replies View Relatedhi friends
can any one help me how to replace a string with another string in a column of table with out effecting the other charcaters
ex:
In srinivas reddy only 'srinivas' has to replace with 'sreenivas'
regards
nukala
I have column data that looks like this:id | ColumnA---+-----------1 | 12,35,1232 | 1,233 | 233,34,354 | 34And I want to be able to make a WHERE clause where I can match up avalue to one of the values between the commas. I.e. each of the numbersbetween the commas are seperate values I wish to compare against.Is there any function, perhaps like VBScripts "Split" function, where Ican split those numbers up and compare a value against them?Perhaps there's another answer someone has?Example:If I'm searching with a value of "12" row 1 would be returned.If I'm searching with a value of "35", row 1 and 3 would be returned.--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
View 4 Replies View Related
i have this string
a string:
ciao; ciao2; ciao3; ciao5; ciao5
they are the same how it is possibile to eliminate the ultimate "ciao5"?
I want that there appears only this string:
ciao; ciao2; ciao3; ciao5;
Thanks you a lot
I am sending a StoredProcedure with some parameter to a database like this:
<code>Dim Conn As New SqlConnection(ConnectionStrings("connBestNok").ConnectionString)Dim sqlString As String = "myProcedure"Dim Cmd As New SqlCommand(sqlString, Conn)Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@Param1", Data.SqlDbType.Int))Cmd.Parameters("@Param1").Value = <myValue1>
Cmd.Parameters.Add(New SqlParameter("@Param2", Data.SqlDbType.Int))Cmd.Parameters("@Param2").Value = <myValue2>
Conn.Open()Cmd.ExecuteNonQuery()
Conn.Close()</code>
How can I find out what the string looks like when sent to the database? I'd like to put it in a Label on my page for better debugging. Using the standard Debug in Visual.Studio .Net do not show the actual string, just the procedure name and the parameters as single entries.
Regards,Roy M. Halvorsen