Select Where String Contains Substring
Oct 17, 2007i'm using sql server 2005 and i need the sql i can use to select rows where the string contains a substring (in access i used instr but now it tells me it's not a built-in function.
View 4 Replies
ADVERTISEMENT
Query For Substring To Get Some Particular Value In A String (Get Querystring Value By Sql )
Jan 28, 2008I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
View 4 Replies View RelatedHow To Return A Substring Based Upon A Character In The String ?
Apr 29, 2008Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields:
select UserName from MyDirectory
NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos
How can I fix this query so that it returns everything to the right of the '' character?
So I want the query to instead return the following:
Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
Extract Substring From String(Regular Expressions)
Feb 11, 2008Hello forum, I need extract a substring form a string that follows a regular expressions -->
T:1º/PQ:1/TALLA:2(MOD.51 100/150)
T:<number>º/PQ:<number>...
I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...
I would like me use it as:
SELECT RegularExpression (Field)
Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.
T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String
Jul 9, 2014I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
T-SQL (SS2K8) :: Trying To Use Substring (charindex) To Remove Start Of A String
Aug 26, 2014Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
I've tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally...
Substring A UniqueIdentifier In SELECT
Dec 14, 2007Is there any way to return a substring value of a UniqueIdentifier in a SELECT Statement, i have been able to Convert the Guid to a String, but when trying to Substring the conversion it failed to recongnize the new column. Any ideas?
Thanks
Select Integer Only From Substring
Nov 19, 2014I would like to select the integer only from the substring of parameter.
The query as below:
DECLARE @Capacity VARCHAR(100)
BEGIN
SET @Capacity = Substring('KK10eads-00l5b1', 2, 4)
print @Capacity
END
The result i get is K10e
However, i only want 10
String Index Function (substring / Charindex) - Extract Specific Characters From Data
Aug 5, 2013SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
Problem In Select Query, Substring
Oct 26, 2007declare @s varchar(50)declare @t varchar(50)set @s='sandeep(hello)fees account'set @t=@sselect substring( @s, charindex('(',@s)+1, charindex(')',@s) )
What i wanted to get is the text in between '(' and the closing ')' but when i tried the above statments iam not getting the right answer
How To Select A Word Substring In A Coumn Header From Right To Left
Oct 10, 2007I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????
View 1 Replies View RelatedCan A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
Apr 6, 2007I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
Regards,
Bill
URGENT HELP PLS: Select Substring('NY',status/1024&&1+1,1) From Master..sysdatabases Where Name=DB_NAME()
Jul 20, 2005Hi Everyone,All the very best for 2004!!i need urgent help with this problem, the users are about to skin mealive!!we have an access front end with linked to sql server 2k tables.when a user tries to insert a record into one of the tables it"randomly" returns a generic ODBC error and fails to save. on otheroccasions the same record will save.a trace was applied and the following select seemed to appear rightafter the insert statement whenever it failed:select substring('NY',status/1024&1+1,1) from master..sysdatabaseswhere name=DB_NAME()i had a look at other articles in the groups re this select statement,but could not find a clear answer.i have tried the insert statements as both SQL pass throughs and justplain docmd.runsql'scan someone help me with the following:* what is the purpose of the select?* what other investigations can i do to get more info on why thisshould be happening?* how can i stop it?the table i am doing the inserts into is showing as have a numericdata type field in sqlserver, but the linked table shows this numericfield as text - could this be the problem?? this field is not used inthe insert statement.i could not find any references in the MS knowledge base.any and all help would very gratefully received.Edwinah63
View 2 Replies View RelatedSql Select String Help
Aug 8, 2005I'm having trouble figuring out how to create the correct select string. My SQL table is like this:tblSoldVehilces Contract Date Stock Number Vin Number Make Model Sale Type PIDI'm trying to find a SQL string that will return a table that I"m not sure is possible.What I need is to get a table like this:Make Model CountMake Model CountWhere it will for instance count all the Honda Accords and give me a total in return so that I can graph all the honda accords sold...so on and so fourth. There is consistancyin the data for model names and such.(ie an Accord is always an Accord and never an accord LX)Any help would be greatly appreciated. I'll continue to read posts to see if I can find the answer.ThanksJosh
View 2 Replies View RelatedSELECT(ing) One DB Column Value Into A String In C#... How?
Jun 18, 2007hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
View 19 Replies View Related' In A Dynamic Select String?
Apr 12, 2005I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '?
For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up.
Any ideas?
Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>
Select String Concatenation
Mar 13, 2006I want to concatanate all text rows returned by the following sql statement:
"SELECT Text FROM PageText WHERE PageTextId = 1"
Table "PageText" has the following columns:
---------------------------------------------
PageTextId (int)
SortOrder (int)
Text (nvarchar(4000))
Is it possible to do this? Kinda like doing a "SUM()" if the values would have been numeric?
Select Data From String
Dec 6, 2007I am trying to extract the numbers only from a string field any help would be great.
field is phone number (123)123-2584 and need to display 1231232584
thanks
SELECT...LIKE: How To Get '100' String Whithout Getting '45,100'
Jul 20, 2005HelloWe've got a string field with some words, and I created a formso that anybody could search someting into this field.SELECT * FROM customer_table WHERE description LIKE '%query%'";But if you search '100', it returns '45,100'. Or if you get'plugged', it reurns 'unplugged'.I tried with:SELECT * FROM customer_table WHERE description LIKE '% query %'";(note the blanks after and before)But it does not work with a word in the beginning of the field.Does anybody have any experience? Thank you very much.
View 1 Replies View RelatedSelect Only Lower Case String
Apr 6, 2007For example,
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
Using String Variables In SQL Select Statements
Apr 13, 2008Hi
I am trying to include a string variable in a Select Statement.
My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:)
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below):
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName
I know there must be something wrong with my syntax ??
Thanks
Jackson
SQL Select Statement With A 'string' Variable?
Apr 16, 2008I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
Converting SELECT Output To String
Nov 23, 2005I'm looking for some good hints and tips for reprogrammin an old VB module I just found.
Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.
E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".
So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @input... the trick is, I don't know how to do arrays in TSQL as in VB.
In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.
Any good examples on how to do this in a sql-server stored proc?
Thanks,
Trin
P.S. This is what I have pieced together this far:
CREATE PROCEDURE findnames
@number int
AS
DECLARE @instrument varchar(50)
DECLARE @tempinstt varchar(10)
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @number
OPEN medlemcursor
FETCH NEXT FROM medlemcursor INTO @tempinstt
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @instrument = @instrument + @tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @tempinstt
END
CLOSE medlemcursor
DEALLOCATE medlemcursor
SELECT @instrument
GO
Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,
SQL 2012 :: How To Select Last Characters From A String
Jun 19, 2014I have the following string and am trying to select only the characters before the last "</>". How can I do this?
declare @String varchar(500)
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))Here is what I tried so far:
[Code] ...
SQL Server 2012 :: SELECT A String
Dec 1, 2014I have string as below:
InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy
I need a string Like below:
Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy
So I need every thing except the First value before first comma .
SELECT WHERE IN Comma Delimited String
Jun 3, 2008Hello
I have a table with column Options where each field contains a comma delimited list of ID numbers.
I want to select any records where a certain ID number appears in that list.
So something like:
SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)
But that gives me the error:
Syntax error converting the varchar value '39,20' to a column of data type int.
I feel I'm close though! Could anyone point me in the right direction?
Many thanks
Square
SELECT Part Of A String In A Column
Dec 23, 2013I'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'.
Select Statement - Get String Between Brackets
Oct 3, 2014I'm trying to run a SELECT statement to get two different values from a field that looks like this:
"Sample text [123], Sample text 2 [345]"
The two values I'm trying to grab has to be after the last comma. So in this case, I need to get
Value 1: 345
Value 2: Sample text 2
Is this possible to do? I can't create functions to accomplish this.
String Compare In Select Statement
Apr 19, 2007is there a way to have a select statement which compares a value is like ('%a%','%b%','%c%','%d%','%f%','%l%')
so :
select address
from customers
where address like ('%a%','%b%','%c%','%d%','%f%','%l%')
???
How To Select A Datetime With A String Of Date?
Oct 26, 2007
Hello, i try to work a my Pocket PC project. I have a question about SQL.
How to write a sql select dataset from table with a give string date but the column is datetime? I don't know how to convert the date string to datetime.Also the time part in the datetime coulmn have some value in it, how to get over that?
and how to make sure both format is correct? because as i check the msdn, there are many different format in datetime.
For example
using date string XX-XX-XXXX to select dataset in the following table
table with following column
DateTimeStamp
XX-XX-XXXX yy:yy:yy:yyyy
Thank you for helping~
SELECT Character In A String (masking)
Aug 15, 2007Anyone who knows a smarter way to select a special part of a text string.
A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.
KnowgoodP_GO02_AA_K_20070807_2010_L.BBB
But the underscore _ sign is like a seperator and will always be there to separate the words.
I need the 20070807_2010 and fomat to 2007-08-07 20:10
SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRRWHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00' between @starttime and @theEndTime
2 Sql Select Statements In 1 Sql String Just Wont Work!
Jul 5, 2007hi there, i have a bit of a strange problem, and i must be doing something wrong because this works with insert and update statements in other pages. What is going on is that i am trying to do 2 select statements in the same bit of code so i dont have to open a datareader twice, but if i put the delivery charge select before the other it seems to get it, but if i put it after it does not, i just cant work it out! i have posted my code below for you all to have a look at because im stumped!
//gets order id and customer id and puts them to strings and int to be used laterstring strCustID = Request.QueryString["qsnOrderCustID"].ToString();
int intCustID = Convert.ToInt32(strCustID);string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID);
//This is the sql statement.string sql = "SELECT [sub_total], [VAT], [Total] FROM tbl_order WHERE order_ID = " + intOrderID + ";" + "SELECT [del_cost] FROM tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader
dr.Read();
//This sets the title label text to the value of the description column.
try
{
//fill these labels with the first select statement lblSubTot.Text = dr["sub_total"].ToString();
lblVAT.Text = dr["VAT"].ToString();lblOrderTotal.Text = dr["Total"].ToString();lblDelCharge.Text = dr["del_cost"].ToString();
//fill this label with the second select statementlblDelCharge.Text = dr["del_cost"].ToString();
}catch (Exception except)
{string strError = Convert.ToString(except);
}
finally
{
myConn.Close();
}
any ideas would be great! thanks
Jez
SQL Select Command.. Allow X Amount Of Characters From A String
Jan 17, 2008Ok so we gotSELECT this, that, others FROM some.database WHERE this=@this So in the database the others field is a string that can have up to 200 characters, but on this particular data pull I only want to pull the first 50 characters of the others field. How can I do that? Thanks.
View 4 Replies View Related