How To Select A Word Substring In A Coumn Header From Right To Left
Oct 10, 2007
I have an application providing me with multiple headers which I have
mergerd into one big header (below), this header my not always be the
same but I need to be able to extract a periodstart and periodend from
it. The periodstart will always be the third substring from the end
(or 3rd from right) and the periodend will always be the first
substring 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 £ Broad
Mkt) 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 this
instance, I need to use someting like the RIGHT function or REVERSE
function but I can't get the sql right.
Can someone please help!????
View 1 Replies
ADVERTISEMENT
May 21, 2008
Hi,
we are using Moss 2007 and in one of our lists we add a hyperlink.
when we display this field into a report it is showed as: hyperlinkurl, name
for example: http://www.site.com,testsite
What we want to is that the piece after the , is displayed as a value and the string before the , is used as hyperlink.
Now we have tested a few things like:
=Trim(Right(Fields!Variant_Locatie.Value, Len(Fields!Variant_Locatie.Value) - InStr(Fields!Variant_Locatie.Value, ", ")))
and this give's us the string part AFTER the , so that part is correct!
but I can't find out how to do the same thing but then for using everything BEFORE the ,
...
anyone got an idea? just changing Right with Left give's us (for example): http://thisisa
instead of http://thisisatest.com (he's counting the number of characters from the right to the , and then displays the characters starting from the left but only the number of characters he counted previously) ...
urgent plz!
View 3 Replies
View Related
Jun 9, 2007
Having a whale of a time, having tried almost every approach, except obviously the right one.
I have employee_no which is character size 10. It is right-justified for some reason. I'm using SUBSTRING to peel off the last five digits (it's going to a flat file where only 5 chars are allowed).
Now I just need to Left Justify the return string. What should I add
to this statement
substring(employees.employee_no,6,8)
My return is this:
A97 1Joe
A97 3Bruce
A97 4Scott
Many thanks in advance, you guys are amazing.
lisa
View 5 Replies
View Related
Oct 18, 2007
Hi,
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
Anyone have an idea why?
Mathieu Desbiens
View 1 Replies
View Related
Dec 1, 2015
is it possible to identify which value is causing me the above error message and how to resolve it,These are for British postcodes.
create table #tmp (postcode varchar(200) NULL)
insert into #tmp values ('NULL')
insert into #tmp values ('-')
insert into #tmp values ('.')
insert into #tmp values ('0L6 7TP')
insert into #tmp values ('AB10 1WP')
insert into #tmp values ('AB51 5HH')
[code]...
This is the main query
select postcode,LEFT([Postcode], CHARINDEX(' ',[Postcode]) - 1)
from #tmp
order by Postcode
drop table #tmp
View 4 Replies
View Related
Mar 17, 2015
I have a SSRS report developed in SQL 2012. When I exporting the report into word the back ground colour of page header missing. When Exporting to PDF and Excel page header BG colour showing.
View 0 Replies
View Related
Aug 7, 2015
I have a report which shows up as below,
Product Type - A
Product Name  - 1
Product Price - Â 1
Product Name - 2
Product Price - 2
Product Type - B
Product Name - 1.1
Product Price - 1.1
Product Name - 2.1
Product Price - 2.1
Product Name - 3.1
Product Price  - 3.1
Similarly i have different Product types and product names and its prices underneath each product type. As you can see, there might be varied number of products under the product type so i am grouping it on Product type. The issue is when i export the report to pdf or word, when ever we have a big group of Product Type( example: Product Type X and it has 10 different Products under it) along with few other smaller groups, the big group tends to jump on to the next page of word or pdf and leaves a big empty space in the previous page. Any way i can break those big groups and  fit  as many as we can on the page and the rest should follow onto the next page rather than leaving the previous page with blank space.Â
View 5 Replies
View Related
Oct 12, 2015
For the past couple of days I've been having an issue with Reporting Services. From what I can understand reporting services doesn't work well when exporting to word.
The scenario is quite simple, I am developing a report for a client with a 'master page', which works as the cover, and executes 2 sub-reports with one table each, nothing fancy. We don't export this report to pdf, because the internal client has to attach another report that is impossible to automate, so they generate that report via excel and integrate it on our word document before sending the document to their business partners.
Our main problem is that we can't export the header/footers to word.
View 2 Replies
View Related
Aug 20, 2015
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage.  There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.  The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date. Â
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
View 6 Replies
View Related
Dec 20, 2006
hello
for MS SQL 2000, i cannot get it
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name
FROM Users
WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN
(
SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users
GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
HAVING (COUNT(*) > 1)
)
does not work
is there any way to use a variable ?
declare @nm nvarchar
set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users
thank you for helping
View 4 Replies
View Related
Dec 14, 2007
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
View 4 Replies
View Related
Nov 19, 2014
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
View 2 Replies
View Related
Oct 17, 2007
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 Related
Oct 26, 2007
declare @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
View 1 Replies
View Related
Jun 1, 2004
I have a strange SQL statement
Select * from TableName WH
Basicly I have a code that creates a Dynamic SQL statement
in this case I thought that I will get an error on the above statement but infact I did not
I tested it on the Enterprise Manager
and it works fine
I changed the WH to WHER it works as long as its not a reserved word
so if I put my name or what ever after that sql it still works
unless I use two words with a space between them it will give an error
on the second one not the first
Can anyone tell me why is that
I do not know if its a feature or what
Thank you in advance.
View 4 Replies
View Related
May 14, 2008
Is it possible to select words that have a certain char size in a given field?
View 1 Replies
View Related
Apr 6, 2007
I 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
View 23 Replies
View Related
Mar 20, 2008
I had database with "-1.#IND" (Indefinite /infinite) values in float columns.
Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.
I want to insert this value to replicate the issue..just for testing. I am also wondring how my database got these values.
Thanks in Advance!
View 1 Replies
View Related
May 29, 2014
In company there are two version of app. New one save order status as "Ordered" the old save as "ORDERED". In order to find out who use old version, how to code in where clause like below?
select userid from order where status = 'ORDERED' (only capital)
(Above code will list all)
View 1 Replies
View Related
Jul 20, 2005
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 Related
Dec 24, 2001
sp_helptext 'sp_myst_proc'
produce
Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure sp_myst_proc --- 1995/11/28 15:48
as
declare @spidlowint
.....
how to remove column heading from output ?
I am putting output to file so I don't want ....
Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
in my file
thanks for any tip .
View 2 Replies
View Related
Jun 9, 2008
Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.
View 2 Replies
View Related
Jun 7, 2004
Hi All,
When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do?
I would like to make a SP that would select all data from any table(with a header row) with just:
_sp_getData ' table_name'
I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.
Something like this:
select 0 AS SortCol ,
cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1,
cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2,
cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3,
cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4,
cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5,
cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6,
cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7,
cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8,
cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9
from information_schema.columns where table_name = 'authors'
union all
select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip,
cast(contract as varchar)
from authors
Is not an option.
Thank you very much in advance,
Vla Orlovsky
View 5 Replies
View Related
Feb 14, 2008
Hi again,
I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber,
dateTimeConnect,
dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1
LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE
(t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND
(t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND
(t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND
(t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND
(@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND
(t1.duration >= @theDuration) AND
((t1.datetimeConnect) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND
((t1.dateTimeDisconnect) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect))
The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value.
What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number).
If this is not clear, please let me know.
Thanks,
Bullpit
View 24 Replies
View Related
Jul 26, 2013
I am currently working with 2 tables:
tbl_users: UserID,Username,ClientFK
tbl_clients: ClientID, ClientName
Now I want to get all Users with same Client but my parameter of my stored procedure is @Username.
This code works fine:
SELECT
UserID,
ClientFK,
WebLogin,
WebPassword,
WindowsUsername,
BasePriority,
IsAdmin,
DateCreated,
Enabled
FROM tbl_User
WHERE ClientFK = (SELECT [ClientFK] FROM tbl_User WHERE [WindowsUsername] = 'Livermorium')
But i don't want to have 2 selects and prefer a left join.
Is it possible to write a better select statement?
View 10 Replies
View Related
May 11, 2007
I think it is quite often when you need to view some records, which refer (by key) to data in other tables. For instance, a user belongs to a group but it is preferable to show group name in the user data rather than group id. The options are
1) LEFT OUTER JOIN:
SELECT users.id, groups.name FROM users LEFT OUTER JOIN groups ON users.[group] = groups.id
2) A Subselect:
SELECT id, [group] = (SELECT [name] FROM groups WHERE id = users.[group]) FROM users
Which is better and why?
View 2 Replies
View Related
Jun 6, 2006
Hi all,
We have sql sever 2000 as our backend database system. on the front, we have vb medical application screen for users to do the data entry.
for 7th screen, it is assoicated with table Case_Custom. user enter data througth 7th screen to populate Case_Custom table.
There is one column called Case_Custom.CaseID ( a foreign key to to parent table Cases) was populated automatically instead of user input.
However, I could not find which stored procedure, views or trigger or application code to populate this field? I have queried INFORMATION_SCHEMA.ROUTINES, dbo.syscomments and did not find anything updating Case_Custom.CaseID.
I have contacted our app vendor and have got any answer from them yet.
Any ideas?
Thanks,
Jane
View 1 Replies
View Related
Apr 18, 2008
My sql statement is running fine in SQL 2000 but it I got the error when running it in SQL2005. The error is "Msg 1013, Level 16, State 1, Line 1
The objects "AL013..gl00105" and "ZZZT1..gl00105" in the FROM clause have the same exposed names. Use correlation names to distinguish them."
This is the SQL statement:
SELECT LEFT(ZZZT1..gl00105.actnumst, 15) as ZZZT1_actnumst,
LEFT(AL013..gl00105.actnumst,15) as AL013_actnumst
FROM ZZZT1..gl00105
RIGHT OUTER JOIN AL013..gl00105 ON
ZZZT1..gl00105.actnumst=AL013..gl00105.actnumst
where ZZZT1..gl00105.actnumst is null
--Checks the reverse relationship
SELECT LEFT(ZZZT1..gl00105.actnumst, 15) as ZZZT1_actnumst,
LEFT(AL013..gl00105.actnumst, 15) as AL013_actnumst
FROM AL013..gl00105
RIGHT OUTER JOIN ZZZT1..gl00105 ON
AL013..gl00105.actnumst=ZZZT1..gl00105.actnumst
where AL013..gl00105.actnumst is null
View 1 Replies
View Related
Jun 22, 2015
Select
left(
[Description],(charindex(';',[Description],1)-1))
from xxxx
Example of Description contains
Ankle Supports; Color=Black; Size=S
So I want the left side up to and NOT including the semi colon.
View 14 Replies
View Related
Sep 16, 2015
My tables look like this:
 Users //
table
 UserID // pk
 UserName // varchar
UserFamilyName // varchar
User_Friends //
table
 FriendsID // pk
 UserID // fk
 FamilyName // varchar
MY query:
 SELECT
U.UserFamilyName, F.FamilyName
 FROM Â
Users U LEFT
JOIN User_Friends
F ON U.UserID =Â
F.UserID
 WHERE    Â
U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
View 10 Replies
View Related
Sep 16, 2014
I have a flat file I need to generate, wanted to create my file from a SQL view.
Is there a way to have a Header and Detail Record for each Record in my view?
Fields would be:
Line no type period ref amt date Inv_no
0 M 1 3/3/2014
1 M Pay inv: 400.00 12345
where 0 is the header and 1 is the detail. Only certain fields will be in the header and others in the detail.
View 1 Replies
View Related
Feb 24, 2008
i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies
View Related
Mar 13, 2008
I need some help. I am writing a report in SSRS 2005 that I then need to export to Excel. When I put a report header I would expect the header to not display in the Excel spreadsheet until the Print Preview or the Print. The report footer works just fine I put some text in the footer, and it shows up in the footer. The header though, shows up as a row in the Excel spreadsheet that then causes columns to merge. How do I get the report header to act like a page header?
View 1 Replies
View Related
