declare @s varchar(50)
declare @t varchar(50)
set @s='sandeep(hello)fees account'
set @t=@s
select 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
Is 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
I 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
i'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 View RelatedI 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 RelatedI 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
Hi 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 Relatedname
--------------------------------------
copd_update_sept2007 9/25/2007 3:24:50 PM
copd_update_sept2007 9/26/2007 1:18:07 PM
feb2008_v2_n2 2/25/2008 1:56:41 PM
feb2008_v2_n2 2/26/2008 2:28:05 PM
i want string like till '' blank spaces..
how can i get that
select substring(name,1,15) from mymaxiddate
I need help capturing information from a free text field.The 10 examples below contain examples of the information I am tryingto capture.In each cell I am trying to capture the number between 'TranID=' andthe next '&'.So in the fisrt cell I would like to capture 14078800.Things you will need to keep in mind are;The number is not of fixed length.'TranID' will always precede the numberThe number will always be followed by an '&'The '&' sign can occur multiple times in the text.Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivRegards,Ciarán
View 8 Replies View RelatedI 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 RelatedPadding leading zeroes for the months in End date
Example: actual data is like
6222007
,11301998
in end date column the following query works fine for 11301998 and converts it as 19981130 which was correct.
But 6222007
fails because month has no leading zero and it converts it as 0076222 which is wrong.
How can i make it as 20070622 with the following code
select (case when replace (ltrim(rtrim(ltrim([end date]))), '|', '') in ('99999999','00000000') then NULL
else substring ([END DATE],5,4)+SUBSTRING([END DATE],1,2)+SUBSTRING([END DATE],3,2) end) as ConvEnd_date
from Mydatabase.dbo.[AccountTable]
Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(__CustomerMachines002.folder_id = @folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
Hi all,
I have just started using SQL7 and quite dumb at it.
Here is my problem
i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.
As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet.
Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.
Any help would be greatly appreciated
Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way
select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0
Arun
Hey you sql programming guru's
I need help seperating a name from first name and last name
The field is like this,
last name, first name
example Doe, John
I need to seperate the last name from the , to the first character
and the last name from the , to the last character.
I think I have to use a substring but not sure how tell it to stop and
start when it gets to the comma.
Can someone please help me.
Thanks,
Dianne
Need to do something like SUBSSTRING in Access, any ideas?
View 1 Replies View RelatedHi
Can anyone show me how to do the following:
I have this snippet of data
ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270
I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.
So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270
Thanks in advance
Hi,
please help me in developing query to satisfy this...
i have table called test and the table data looks like this.
col1
123.abc
1.ab
12.cba
the query needs to return all rows data after . the result set should return this
abc
ab
cba
appreciate your help..
thanks
sskris
I have a field that contaings 15 characters. I want to just pull the first 6. So anything that matches these 6 will be returned.
View 10 Replies View RelatedWe have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.
ACustomerName
ACustomerNameCredit
ACustomerNameDebit
Thanks!
I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:
Coulumn1
-------------------------------
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210
I tryed this:
use cimpro1
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
from opcshto
For some reason, when I run the query I get this for State:
State
-------------------------
CA91733617
LA71172
CA90249107
WI53216
IL61944
IL62832
MN56283
ME04210
When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.
Any help is appreciated.
iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error
query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."
But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @
where did i go wrong
i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code
select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks