And I want to be able to make a WHERE clause where I can match up a
value to one of the values between the commas. I.e. each of the numbers
between the commas are seperate values I wish to compare against.
Is there any function, perhaps like VBScripts "Split" function, where I
can 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.
I am trying to select the last 3 characters from a string. I am running into problems because the sting that I am selecting from are not the same amout of characters.
For example:
Item
abc145264 efg1254 wqx21456
How would I be able to select the last three characters from a list that could have more than 50 variations on the number of characters.
I tryed right(item, 3) but that does not work because all the lenghts are different. Any ideas?
Hello,I need to be able to select only the numeric data from a string that isin the form of iFuturePriceID=N'4194582'I have the following code working to remove all the non-numeric textfrom before the numbers, but it is still leaving the single quote afterthe numbers, i.e. 4194582'Any ideas or suggestions how to accomplish that? Thanks in advance.Declare @TestData varchar(29)Set @TestData = "iFuturePriceID=N'4194582'"Select Substring(@TestData, patindex('%[0-9]%', @TestData),Len(@TestData))TGru*** Sent via Developersdex http://www.developersdex.com ***
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.
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.
I 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?
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
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
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.
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.
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.
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
is there some way to allow this to execute, using a function or something?:
select * from 'cusomer'
you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??
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
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.
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?
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
hello! I have a code folow: public string GetLink() { string ID=Request.QueryString["mabaihat"].ToString(); string get; SqlConnection con = new SqlConnection(strconnection); DataSet ds = new DataSet(); con.Open(); SqlCommand cmd = new SqlCommand("select link from song where songid="+ID+" ",con); // how can I get string of comlumn link? // con.Colse(); return get;//string link }
Hi! I am having trouble formating this string in c#: string sqlinsert="INSERT INTO tblWebsiteAssociation (UserID,CustomerID) VALUES (1," + item + " )"; I am having problems w/ the quotes, what's the best way to do this? Thanks!
Im using MS SQL Server I need to add 2 case statements together and return the value. Im able to see one value at a time but when I try to add them Im returned nothing. here is the statement CASE WHEN FLID01 = 'X' THEN SUM(ARAM13) END + CASE WHEN FLID01 = 'E' THEN SUM(ARAM27) END AS EXPR1 . Can anyone help me with the correct syntax thanx weisenbr
i have a sqlString to select records from database as below,
StrSql="Select Distinct ItemCode From StockOnHand Where " & _ "Description like @Des"
this sql string works correctly, but i need to display those distinct ItemCode with their Description and Price, but i failed using the sql String at below...
StrSql="Select Distinct ItemCode, Description, Price From StockOnHand Where " & _ "Description like @Des"
I know there is something wrong with my sql string.. but my SQL knowledge is onli at the basic level, can any1 pls help me on this...?
Hi, I've got a simple table of employee data which has an id defined as a char field and is also nullable. The data is stored alphabetically sorted in the database, by last name. Now I'm inserting new employee records, but they're not being added in alphabetical order and of course the id's are null. Other than dropping the id field and recreating it as a numeric identity field, what are my alternatives? thanks
