I have a column Sports which contains a string.
the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.
What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?
(I can't search on ",2," since "2" may also be at the start of the string....)
I have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>>Â and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies View RelatedOk, so I have a Select statement that looks like this:
Select * from FilteredOpportunity
where (OpportunityID LIKE @opportunityid)
and (Name LIKE @name)
and (CustomerIDName LIKE @customeridname)
and isNull(EstimatedCloseDate, '1900-01-01') LIKE @estimatedclosedate
and isNull(Description, '') LIKE @description
and isNull(AccountIdName, '') LIKE @AccountIDName
and isNull(ACCU_OppTypeName, '') LIKE @ACCU_OppTypeName
and isNull(SalesStageCodeName, '') LIKE @SalesStageCodeName
and isNull(ACCU_CustomerTypeName, '') LIKE @ACCU_CustomerTypeName
and (CreatedByName LIKE @CreatedByName)
and (CreatedOn LIKE @CreatedOn)
and isNull(ACCU_OppSourceName, '') LIKE @ACCU_OppSourceName
and (StateCodeName LIKE @StateCodeName)
and (StatusCodeName LIKE @StatusCodeName)
This chunk of code is used in an XML file which, once this XML is imported (using sharepoint by the way if you haven't noticed), I can then search through tables with different filters. Well, my date filter isn't working the same way as my other ones. I have a couple different date filters, but none of them work.
Before I ask my question, I only pass in one variable at a time through my code.... the rest of the variables get a default value passed to them if the user doesn't pass it. That value is simply '%'. Anyways, so the different ways a user can type in a date is by typing in the whole date, part of the date starting with the beginning of it, typing in the end of the date, or typing something within the date. For a string it's like 'Example', 'Exam%', '%ple', and '%amp% respectively. How do I accomplish this with a date?
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
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'm having problems constructing an SQL statement to find records from a user entered string searching record. a record consists of a comma separated string. for example:
user entered string: charts
record: bars, graphs, charts
i've tried:
SELECT MI_FILENAME, MI_EXT, MI_KEYWORDS
FROM MEDIA_ITEMS
WHERE (MI_KEYWORDS IN ('bars'))
Could u offer any assistance please
Searching through a database with data stored as text places a huge load onthe server. I've seen some applications that use some sort of word indexingthat helps speed things up.Is this the recommended strategy? And if so, how is this implemented andare there any articles that detail this?
View 1 Replies View RelatedHi,Does anyone have any suggestions on an approach to speed this upplease?I need to check a given column in a given table to see whether thereare any characters within any of the records that fall outside of arange of valid ASCII characters.My problem is that of speed. The approach I have taken is to write afunction that iterates the string character by character and checks theASCII value at the current position. If it is deemed invalid I drop outof the function and return True. The calling procedure then knows tostop searching as the table column has already failed.Running down a table of million rows, can take up to an hour dependingon the datatype and level of data population.If I write the same thing in Access 2003 VBA and run it via ODBC ittakes a matter of minutes.I would like to find a set-based way of doing this maybe PATINDEX orsomething else creative but am stumped.Does anyone have any brilliant ideas?Thank you!
View 5 Replies View Relatedhihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
View 1 Replies View RelatedNeed help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanx
Hi,I'd be interested in people's thoughts about the following. A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for. Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue. The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!
View 1 Replies View RelatedI am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad
Hello,
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
how can you return part of a string and convert it into an integer value? Maybe like this:
convert(left(column, 3) as int)
does that work?
The Yak Village Idiot
Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.
View 4 Replies View RelatedHi,
I have 2 questions.
1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
The length of "Name" varies and I want to extract all characters included for "Name".
Can anyone advise what the query/SQL statement would look like?
2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.
So I want to include a code that checks if file "file.xls" is open on user's machine.
If file is open, then message "file "File.xls" is already open. Generating File_1.xls"
Run the code but create the file with file name "file_1.xls"
If file doesn't exist, then run code and create file with file name "File.xls"
Please advise.
My problem is that i can't search a field that contains Asian characters (Korean in this case).
The table user_Access_tab have 2 keys:
access_id nVarchar(50)
user_id nVarchar(50)
The sql query below is sent through a oledbcommand to a sql server 2005 database.
"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'"
It doesn't show any hits even thogh i know there is a number of matching records.
The question doesn't generate an answer in SQL server manager studio eigher.
If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:
HQ001kimjo012007-05-07 ?? 11:50:323401
Then the question works, but why doesn't it work with nVarChar(50)?
Regards Martin Jonsson
I'm trying to form a query that will select part of a result.I'm trying to take out the ending of results that end in '-PR'.
Example: The original result is 'Jbbx32-PR'. I want to select it as 'Jbbx32'.
I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this?? thanks
View 7 Replies View Relatedhi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column.
thanks
Ahmed
I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.
"GROUP BY LIKE '%ThePartToGroupBy%'"
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
I want to extract a number within a column results, that is the number between the first two commas
Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....
How to extract the second number(between the first two commas) from the above results?
The output should be
1158286
1158289
I am using SQL Server 2008. I have strings like this:
AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)
I want to split the string with delimiter '-' and store as first part and second part.
I saw few sample functions to split a string but these return table of values.
I simply want first part and second part.
In above examples context:
@First_Part = 'AB'
@Second_Part = '123'
Any simple way to do this?
Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:
- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums
Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?
Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.
Appreciate the responses, Onam.
Hello all,
I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:
Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.
I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.
It is also important to note that the rest of the body of the emails stored here is different depending on the email.
So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.
Thanks for the help in advance!
I'm trying to replace a particular part of a row in a table with a new value.
The row is called "DataPath" and it has a lot of values like so:
mashOperationsComponent Data FilesSantec
I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.
I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..
UPDATE dbo.Part
SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash')
WHERE DataPath like 'mash\%'
So for example, it would change the mash above to
company.localsharesDepartmentsOperationsComponent Data FilesSantec
i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.
like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much
CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END
CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id      MyString
1 Â Â Â Â Â ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id      DesiredString
1 Â Â Â Â Â Extracts^This^String1
2 Â Â Â Â Â Extracts^This^String2
3 Â Â Â Â Â Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):
<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">
I would like to replace this part of the string with a different piece of text.
I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar