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).
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.
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) ...
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
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!!!!
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.
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.Â
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.
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,
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
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?
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.
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
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.
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)
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
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 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.
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
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
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
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.
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
 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.
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?
