Retrieve Only Strings That Hold Sub-string In Middle

Nov 24, 2013

I need retrieve only strings that hold the sub-string in middle. no first no last just middle.

See: the sub-string is "test"

Yes: test test test
Yes: take test test
Yes: test test take
Yes: take test take
No: test test
No: test take take
No: take take test
No: test

I prepare sample data...

declare @sample table(s varchar(50));
insert @sample values
('test test test'),
('test test'),
('test'),
('test take test'),
('test take take'),
('take test test'),
('take test take');

View 4 Replies


ADVERTISEMENT

How Do I Use A @variable To Hold On Value Return From An Exec ('string Command') Statement.

Sep 19, 2000

How do I use a @variable to hold on value return from an exec ('string command') statement.

Example for:

declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)

set @db = 'DBNAME'
set @OldOwner = 'USERNAME'

select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)

How can I use @OldID to hold on UID return from this statement.

When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.

Thanks.

View 2 Replies View Related

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

Retreive The Middle Of A String

Apr 5, 2007

Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???

Thanks :)

View 7 Replies View Related

How To Get String Value In A Middle Position?

Feb 26, 2007

khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."

View 3 Replies View Related

Different Conn Strings To Retrieve Different Datasets

May 8, 2008

I have a Save method in my class file that connects to my sql db to 4 datasets, which I then wrap together. I am currently instantiating 4 different Sql Client connections to do this.
Would it be more efficient to use just one?
 
 
 

View 2 Replies View Related

Extract Data From Middle Of String In SQL Server

Jan 11, 2008

I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:

DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_

I want to select the 8 characters after the first comma in the field, and then convert it to a date format.

I am a novice, so any help with the correct function and syntax would be greatly appreciated.

thanks,
Dave

View 8 Replies View Related

Retrieve Multiple Roleid's By Passing Csv Strings

Jun 24, 2008

Dear All
I have a table Roles with columns(RoleId,Rolename)
I have another table userrole with columns(userid,roleid)

I need to retrieve the roleids when rolenames(multiple strings in csv format) are passed.
The stored procedure is as follows
create procedure GetRoleIds(@Rolenames varchar(200),@RoleIds int output) as
begin
end
declare @roleidlist int
Exec GetRoleIds 'Admin,Manager,Developer',@roleidlist output
print @roleidlist

The output must be 1,2,3.

Roles table data

Roleid rolename
1 Admin
2 Manager
3 Developer

How to do this.Please help





Thanks
srinivas

View 20 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.

I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

View 9 Replies View Related

Delete Right And Left Part And Show Middle String?

Oct 31, 2014

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

View 3 Replies View Related

SQL Server 2008 :: Not Finding A Record With A Space In The Middle Of String

May 19, 2015

Ok following scenario

create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')

So if execute the following

select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'

I get both records,but if I do this

select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)

I only get AIRLBEOF3565

So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.

View 4 Replies View Related

Updating The String Removing The Strings.

May 20, 2008

I have to write a query to update the table to remove dots .... from the string value.
so i could write.

UPDATE tbaddress
SET Title='New address'
WHERE Title='New address....'

but, there could be more than this specific record in the table, so i have to find each single record to do this. Is there a way to update the table so I can just remove the .... but rest of the text remain.

thanks

View 2 Replies View Related

T-SQL (SS2K8) :: How To Parse A String To Equal Length Sub-strings

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

after parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow..So the parsing should be dynamic.

View 2 Replies View Related

Can't Retrieve Connection String From Web.config - ASP.NET 2.0

Aug 1, 2007

All,
 I don't know what is the problem, i am not able to retrieve connection string from web.config  in my code behind page
I used every solution provided by people in differents forums.
can anybody help me with this.I used
WebConfigurationManager.OpenWebConfiguration("/TimeSheetApplication").ConnectionStrings("TimeSheetConnectionString").ConnectionString;
WebConfigurationManager.ConnectionStrings("TConnectionString").ConnectionString;

View 5 Replies View Related

How To Retrieve Large String From Stored Procedure?

May 4, 2007

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.

View 6 Replies View Related

SQL Server 2012 :: Retrieve String Between Two Delimiters For Multiple Occurrences?

Oct 21, 2015

How can we get the result set as TESTING1,TESTING2 from below

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $

View 6 Replies View Related

Data Access :: How To Retrieve Remote Database Table Value To String Variable

May 28, 2015

I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it. 

And my code is :

string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();

View 6 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

Sp Hold

Aug 23, 2005

Hi all,

Just wondering if anyone could help...

I have a usertable which adds anyone whom is new to the records. However the site has had quite an increase in traffic and the stored procedure (SP) The problem is that if two new users call the SP with say a second of each other the sp tries to enter the same user key.

The sp basically checks the last id and add 1 on to it.. Simple but inbetween checking and interest someone else is doing the same creating a violation ...

Basically ineed to put a hold on the select and intert until complete... A sort of que up ...

Sorry if it a dumb question...

ps i've read about changing the entire conection but i only need it for this SP...

Thanks

View 3 Replies View Related

Does SqlDataSource Hold The Connection ?

Mar 9, 2006

If we bind a GridView to a SQLDataSource, is it not a connected usage, which locks up one connection thread ?

View 2 Replies View Related

How Long Declare @ Can Hold On?

Dec 20, 2006

Currently i have a long long query may run 10 hours
but i wonder if i declare :
DECLARE @rows int

how long it will hold on its value??

View 4 Replies View Related

How Many Tables A Database Can Hold?

Jun 19, 2008

Hi everybody,

Generally, How many tables a database can hold?

Thanks,
Dhevi.S

View 5 Replies View Related

@local_var Really Cannot Hold Table Name ?

Jul 20, 2005

Hi,I have tables named like ag97a027, ag98a027, ... where 97 and 98 isfrom year 1997, 1998 and a027 is just specification of product type. Ineed carry out repeated operataions over these tables. To generatetheir names is easy, for exampleuse [P5-01]declare @Yint int, @Y char(2), @tabName char(8)set @Yint = 1997while @Yint < 2002beginset @tabName = 'ag' + substring(convert(char(4),@Yint),3,2)+'a027'print @tabNameset @Yint = @Yint + 1/*select *from @tabName*/endprints properlyag97a027ag98a027ag99a027ag00a027ag01a027but when I uncomment the select statement, MS-SQL server responds:Server: Msg 137, Level 15, State 2, Line 11Must declare the variable '@tabName'.How to use @local_variable for browsing through tables in abovedescribed way?thanksMartin

View 3 Replies View Related

Variables To Hold Filenames

Feb 8, 2007

I was wondering if it is possible to create a variable that holds the filename as an expression. I am using a For each loop to pick up files and i want to insert the 'Filename' as a colum in to the destination Table so that i can differentiate among the rows in my destination Table.

Using the 'Derived Colum' in DTS I can link the colum to a variable. However i do not know how to create the variable which will dynamically store the filename for each file....

Any Clues...
Would really appreciate the help.....

Or is anyone knows any other simple way of inserting the filename as a column in the destination Table ...Been stuck on this for a while...

View 6 Replies View Related

How Do You Hold The Value Of A CASE Statement For Further Use In The Query??

Feb 18, 2007

I have a select statement and I would like to know which when clause(1st set of whens) was fired and the value of the then for further use in my query.  I am wondering if this is possibly, and how it would be done.  If i try and set a @variable at the beginning of the case statament i get an error, here is my query         SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length,                 ***throws an error**** @sqft = CASE  --- but the then clause of this CASE is the value i want for later use in this same query                                 WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0                                 WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                                 ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                         END AS sqft,                         CASE                                 WHEN @custMarkup = 1 THEN (SELECT PML1 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 2 THEN (SELECT PML2 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 3 THEN (SELECT PML3 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 4 THEN (SELECT PML4 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 5 THEN (SELECT PML5 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                         END AS markup         FROM ProductsInEstimate PIE         WHERE estID = @estid

View 1 Replies View Related

How To Get Hold Of Data From Web Provider Using SQL Server?

Feb 16, 2006

I am new to SQL server so apologies if this is a stupid question.



I have a website built and hosted by another company, with SQL server
back end data. They don't want to analyse the data but I do. I am
familiar with SQL and have SQL server express running on my laptop.



So I was thinking I could get them to send me the data from the server
(it will be less than 1GB I imagine), and I could analyse it myself.



So my questions are:

will this work?

and what do I ask them to send me? Is it just a couple of files they
have to copy and send me? Or is it more complicated than that?



Many thanks for any advice you can offer.



James

View 1 Replies View Related

Can I Use A Variable To Hold The Target Table Name.

Apr 11, 2007

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.

View 13 Replies View Related

Many Data Entry Tables - Which Ones Hold Records?

Oct 23, 2005

We have an asp.net app with about 200 data entry forms. Customers mayenter data into any number of forms. Each form's data is persisted ina corresponding sql table. When data entry is complete, it needs to beprocessed. Here's where the questions start.How can we easily determine in which tables a customer has data and howbest to select that data?We're not opposed to putting all the data in a single table. Thistable would wind up having ~15 million records and constantly have CRUDoperations performed against it by up to 5000 users simultaneously.With sufficient hardware, is this too much to ask of the db?

View 10 Replies View Related

Variable To Hold Two Digit Month In A Expression

Mar 27, 2007

I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.



SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')



This works perfect when running in T-SQL, but when attempting to define a variable in the expression

REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?



Anyone run into a simliar issue or have a suggested work around?

View 7 Replies View Related

Which System Tables Hold SSIS Pkg Information

Apr 4, 2006

Hello Everyone,

I need to know which system tables hold the pkg information when you import a pkg into SQL Server 2005.





Thanks

Shabnam

View 3 Replies View Related

.NET Class To Hold Single Disconnected Record? Nothing Smaller Than DataSet?

Jul 9, 2007

What is the most efficient standalone .NET class that can hold a single disconnected record?  The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon 

View 7 Replies View Related

SQL 2012 :: Design A Table To Hold Filters For Selection Criteria?

May 5, 2014

I have an ordering database with several tables that store data of orders belonging to a wide variety of clients. There is a generic report that I need to run which outputs the same data elements. However the criteria to select these orders will vary widely between each client. For e.g.

i) for client# 1 it could be all orders that are still open after 30 days of placing an order

(
OrderStatus = 'Open'
AND
GetDate() - OrderCreationDate >= 30
)

ii) for client# 2 it could be all orders that have been completed 60 days or earlier

(
OrderStatus = 'Completed'
AND
GetDate() - OrderCompletedDate >= 60
)

iii) for client# 3 it could be a combination of different things (all orders in West Region that are in hold status for more than 10 days + all orders in Eastern Region that are in shipping and are expected to be delivered in the next 2 days + all completed orders for the rest of the regions).

(
OrderRegion = 'West'
AND
OrderStatus = 'Hold'
AND
GetDate() - OrderHoldDate >= 10

[code].....

I want to have a stored procedure that selects all data and dynamically attach the where condition at the end for filtering. This way I wouldn't have to worry about any additions/changes that are made to the selection criteria. I can build an interface for admins who can use the UI to maintain the selection criteria and not worry about any code changes to accommodate it. I would like to design a table that holds this criteria. At this point in time, I am thinking of using key value pairs (Column Name, Column Value) but I am not sure how to implement multiple logical operators.

View 4 Replies View Related

Average Of Middle 90%

Sep 21, 2007

Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved