Find Junk Patterns In Employee Name
Jul 6, 2012
string starts with 3 digit same number
111 H,777GGG,9999 H etc
string starts with NULL
string that starts with sequential digits
123g,897 k
string that starts with sequential alphabets
abcmki12, ghimkkk, rst123 5 etc
string only one character (digit or letter)
string only has same character repeated 3 times except for OOO
string only has three characters, 1 digit and two letters or 2 digit one letter
string that has only two characters one digit and one letter
View 1 Replies
ADVERTISEMENT
Feb 29, 2008
Thanks in advanace for taking the time to read this post.
I am using MSSQL 2005 and have created a function that allows me to use regular expressions in my SQL queries.
My question is I have a pattern buried in a field of misc data that I need to pull out just that pattern and discard the rest of the data. Here is the Regular Expression I am using
select field1 from table1
where dbo.RegExMatch (field1,'[a-zA-Z]{4}[0-9]{6}[a-zA-Z]{2,4}')=1
This returns all values in the field that match the expression. What I want to do now is remove all data from the field on the left and right of the expression that does not match the expression. How would I accomplish this without reading through the 200k+ records and writing rules for every exception I run across?
so I could have Gar b/a ge 'THE GOOD DATA' m/or1 ba4d da....ta. All I want to do is return 'THE GOOD DATA'
View 4 Replies
View Related
Aug 18, 2015
IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = '#EmpHours' )
DROP TABLE #EmpHours;
CREATE TABLE #EmpHours
[Code]....
View 7 Replies
View Related
Oct 25, 2012
Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins
Note:- also need records who doesn't have manager (null)
table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5
o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.
View 9 Replies
View Related
Jun 7, 2015
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
View 3 Replies
View Related
Aug 14, 2014
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.
View 4 Replies
View Related
Feb 21, 2007
hi all, what sql data type should i use to store variable length bit patterns no longer than 30 bits?
View 4 Replies
View Related
Feb 13, 2008
Design patterns are indispensable to improve the quality and productivity of system under development. Even though there are books available addressing the needs of object oriented programming, but useful information for SQL design patterns are hard to find.
I would appreciate if you could share any of the resources that you may have come across.
View 21 Replies
View Related
May 9, 2006
I am looking for something similar to patterns for table design, but I have been unable to find anything on that, or good rule of thumb performance guides for table design.
Most of the situations we face look like perfect candidates for patterns, and some good rules of thumb -- problems like scalable OLAP requirements PLUS real time reporting.
I am looking for several differnet approaches and a good summary of the trade-offs for each one.
Thanks!
View 1 Replies
View Related
Sep 26, 2007
Hi,
I am Suhasini. While saving data from front end(Asp.net) to back end(Sql server 2005 express edition) i am getting junk characters also added to the database. This character just look like a checkbox. Basically i am adding options using a multiline text box, is there any thing wrong with that. options are saved in the database as junk character followed by option1...... etc. Kindly suggest me on this.
View 3 Replies
View Related
Aug 24, 2007
How to find and replace the Junk Characters which is accidently stored in the "TEXT" data type column of one table.
View 5 Replies
View Related
Oct 17, 2007
Hi,
I am trying to design some functionality that emulates the recurrence functionality of MS Outlook calendar. In the calendar, users can choose a recurrence pattern (i.e. daily, weekly, monthly or yearly). Depending on the radio button that is selected, different choices appear. I am unsure as to how to model this in the database.
Here's what I have so far:
A ScheduleDetails table that will store information applicable to all schedules, regardless of recurrence pattern. One of the columns in this table will store the recurrence pattern type (0 for daily, 1 for weekly, etc.). The valid values for this column will be stored in a look-up table.
Now, if daily is selected, the user can select a radio button called 'Every <#> days' OR a radio button called 'Every weekday'. My plan is to have a new ScheduleDailyRecurrences table that has a Days field. If 'Every <#> days' is selected, the value of Days will be the value entered by the user. Otherwise, if the user has selected 'Every weekday', the value will be -1. Is this a good way to do it? I feel as though I may be attaching too much meaning to the particular value.
If monthly is selected, it gets even more complicated. The user can either select 'Day <#> of every <#> month(s)' OR 'The <count> <day> of every <#> month(s)'. I'm not sure how to model this. In my ScheduleMonthlyRecurrences table, should I have a field called 'Pattern'? Depending on the value of this field, the details would be then be stored in yet another table? Or should I just have columns in ScheduleMonthlyRecurrences for each user-entered value? This would mean that some of the fields would not be applicable depending on the radio button that the user has selected.
It's a little difficult to explain, but if you go to the Recurrence button in Outlook calendar, you'll see what I mean.
Thanks for any advice.
View 5 Replies
View Related
Feb 17, 2008
I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.
Table structure for one min data:
[Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI].....
Tick Data:
[Date] [Time] [Trade]
Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like:
With IntervalData AS
(
SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430',
Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431',
Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432'
FROM [INDU_1] GROUP BY [Date]
)
SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData
WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0)
------------------------------------------------------------------------
select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1
INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date]
where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0))
ORDER BY ind1.[Date] DESC;
Is there anyway I can use Sql 2005 Data mining models to make this searching faster?
View 1 Replies
View Related
Sep 5, 2007
---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Begin
Print 'Not all numbers'
End
Else
Begin
Print 'All numbers'
End
---Checks that input only contains letters
if PatIndex('%[^a-z]%','aaaaa') > 0
Begin
Print 'Not all letters'
End
Else
Begin
Print 'All letters'
End
--Checking for mixed input
If PatIndex('%[^0-9][0-9]%','abc') > 0
Begin
Print 'Alpha numeric data'
End
Else
Begin
Print 'Either all numbers or all letters'
End
--Checks that value must start with a letter and a number
If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0
Begin
Print 'Starts with a letter and a number'
End
Else
Begin
Print 'Does not start with a letter and a number'
End
--Checks that value must End with a letter and a number
If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0
Begin
Print 'Ends with a letter and a number'
End
Else
Begin
Print 'Does not End with a letter and a number'
End
--Checks that value must Start with a letter and Ends with a number
If PatIndex('[^0-9]%[0-9]','namwar1') > 0
Begin
Print 'Starts with a letter and ends with a number'
End
Else
Begin
Print 'Does not start with a letter and ends with a number'
End
Namwar
<Link removed by georgev>
View 13 Replies
View Related
May 16, 2012
I have a report with a chart that utilizes both colors and background patterns to distinguish various pieces of data.
The background patterns work great (they are very clear and provide excellent contrast) when viewed in the report viewer or printed straight from the report viewer.
However, when I export to PDF, the background patterns become compressed and small, almost to the point of being too small to distinguish that there is a pattern at all. The result ends up being what appears to be just a different shade of the background color. This is particularly a problem when you then print the PDF on paper, since hatching patterns are similar to how printers create different shades in the first place.
I am somewhat suspicious that this might have to do with resolution settings for PDF exports. If I could lower the resolution, perhaps the patterns would not become so tight and compressed. Below are examples of what I'm describing:
Screenshot of the report viewer:
Screenshot of the PDF:
View 2 Replies
View Related
Apr 27, 2006
My application has a log table with a timestamp field which represents the time when the record was inserted.
We would like query the the table and group the results into date units based on the value of the timestamp.
The grouping specification is held in another table but only specified for a single week.
For example
WeekDay Start End
1 06:00:00 14:00:00
2 08:10:00 17:00:00
What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...
The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.
I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.
Thanks in advance.
View 5 Replies
View Related
Sep 18, 2006
Hi
We are generating the log file using SSIS built in logging feature. It is generating a text file as "log.txt".
Again we need to add few string values like "Files Loaded Successfully". So i tried adding this string using the following 2 ways.
1. Using Script Task in SSIS
2. Using SQL Stored procedure.
The following are the code:
Through SSIS Script:
Contents = "File Loaded Successfully"
'Get a StreamReader class that can be used to read the file
Dim objStreamWriter As IO.StreamWriter
objStreamWriter =
IO.File.AppendText("\Chnbslsql2k3shareLoglog.txt")
objStreamWriter.Write(Contents)
'Close the stream
objStreamWriter.Close()
Through Stored Proc:
CREATE PROCEDURE [dbo].[LG_Create_LogFile]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@cmdstr as nvarchar(1000),
@Msg AS varchar(1000)
BEGIN TRY
BEGIN
-- Create move command
set @Msg = 'File Loaded by Kumaran '
set @cmdstr = 'echo ' + @Msg + ' >>
\Chnbslsql2k3shareLoglog.txt'
END
-- Execute command
EXEC master..xp_cmdshell @cmdstr
END TRY
BEGIN CATCH
RAISERROR('FILE NOT MOVED SUCCESSFULL', 16, -1)
RETURN -1
END CATCH
END
In the both ways, it is giving me junk characters as like in the below output log.txt file...
PackageStart,CHNBSLSQL2K3,NT AUTHORITYSYSTEM,pkgExtract,{93BE582E-EDDD-4EBD-97A4-8A2E39E839A9},{605B2ED1-D794-4455-BD9E-F32D4CFF1196},9/18/2006 8:43:40 PM,9/18/2006 8:43:40 PM,0,0x,Beginning of package execution.
OnError,,,pkgExtract,,,9/18/2006 8:43:43 PM,9/18/2006 8:43:43 PM,-1071636466,0x,Cannot open the datafile "\Chnbslsql2k3shareRawCPSExtract.dat".
PackageEnd,CHNBSLSQL2K3,NT AUTHORITYSYSTEM,pkgExtract,{93BE582E-EDDD-4EBD-97A4-8A2E39E839A9},{605B2ED1-D794-4455-BD9E-F32D4CFF1196},9/18/2006 8:43:48 PM,9/18/2006 8:43:48 PM,1,0x,End of package execution.
æ¥†æ•¬ä° æ…¯æ•¤?¤ç¥¢ä¬ 浵牡湡€ à¨?
This is a issue which i am struggling and looking on..Please help me out with some gud
solutions.
Thanks
Kumaran
View 1 Replies
View Related
Jun 3, 2007
Hai all,
We have quite a strange problem, we have some SP's in our project which is functioning quite well all these days. Suddenly one day we got an error in the SP stating "Invalid Table Name", when we opened the SP and saw in some places the Tablename was replaced with Junk Characters inside the SP !!!!!!!
For Ex: Inside the SP we have the following SELECT query
"SELECT F1 FROM SampleTable"
the above query is getting replaced with
"SELECT F1 FROM SampleTa?le"
The junk character actually appears like a box!!!.. we were absolutely clueless why this was happening. Then if we change the SP once agin and run it starts to execute but only for a short time and the problem comes back once again. When we went through the SQL logs we got this error repeatedly:
"Error: 17805, Severity: 20, State: 3
Invalid buffer received from clients"
We came to know a little about this problem from kb articles in MS that when you are calling an SP from .Net u should specify the parameter data types explicitly and u should not use SQL Client in Finalise method all which we have not done in the application.
Tha SP was using #Table which we replaced to @Tablevariables but still we got the problem once, we are monitoring with the same Table Variables.
We are using SQL Server 2000 SP4. We have got stuck with this problem for days now . Any help is greatly appreciated.
Thanks in advance.
View 1 Replies
View Related
Aug 29, 2007
sample Data: "061107"
Every flat file ends with the junk character
How to remove this using "Derived Component" or "Script Component" ?
Pls provide the sample code for this task. Thank you.
View 6 Replies
View Related
Feb 23, 2006
Back Story...
i am building a new deployment routine for one of our products using NANT (.net version of ANT). I takes all of .sql files out of vault for all non table objects and concatenates them into larger .sql files with IF EXISTS .... DROP ... GO CREATE statements which will then be fired off by osql batch files that will log the errors.
Problem...
When I open the resulting files in notepad I have junk characters (microsoft OEM stuff) in some of my object definitions which you do not see in the QA or if you opnen the file in Vault. I need to get this stuff out of there and I do not want to write a bunch of filesystem code to do it because I need this tested and ready by Friday COB. I know I can solve this by opening the problematic files in ultraedit and saving them one by one but I do have that kind of time.
Any ideas?
It's going to be a long couple of days.
View 14 Replies
View Related
Mar 12, 2012
I have a table and one of the column have junk characters in it, how can I remove the junk characters?.
Eg : Employee
Eid Ename
1 a�
2 �ddd
how can i remove Junk characters and get only Enames from above table.
View 4 Replies
View Related
Mar 30, 2012
I developed a mass mailing tools, but all mails received in junk and spam folder
I use an store procedure 'sp_send_dbmail' which is by default in sql server, when i send an testing mail using object explorer ->Database mail -> right click -> send testing mails, then this mail received in inbox, but when i call 'sp_send_dbmail' this procedure using job with 2 min intervals then mails received in junk or spam folder of recipients..
I configured correctly all information of smtp details ip address...
Why mails r received as junk or spam folder ??
View 2 Replies
View Related
Apr 18, 2012
i got stuck, while sending emails using store procedure sp_send_dbmail, when i m sending simple text format then mail received in inbox but whenever i m trying to send an html format emails with links of images and href tags mails received in spam.. i store procedure is given below..
DECLARE @tableHTML varchar ;
SET @tableHTML =
'<table border="1" >
<tr>
<td width="187"><a href="http://printbooth.in/product-list.php?cat_id=16&subcat_id=265" target="_blank"><img
[code]...
View 1 Replies
View Related
Mar 31, 2008
Is there a way to tell SQL Server 2005 Management Studio that I don't want it to include "USE [dbname]" and "/****** Object: Table [dbo].[TBL_NAME] Script Date: [date] ******/" when it creates scripts? I searched the forums and couldn't find an answer, and I don't see it in Tools->Options.
View 5 Replies
View Related
Jan 17, 2007
I have a varchar field which should contain dates but unfortunately it is fill with all sorts of imaginary formats which only the one who has typed may understand.
9+03/24/2006
3/16/2006
11:15AM
5-Oct
8/8/2006
9
.
`06/27/2006
12505
1/9/2007
1/12/2007
1/12/2007
1/15/2007
in a data set of 15000 rows most of them are like '1/15/2007'
and 305 roes are like the rest in the sample.
can you sugest ma a simple way to convert posible data to format '1/15/2007' format which i can use in a SELECT...INSERT STATEMENT and convert the rest to NULL
View 7 Replies
View Related
Jul 20, 2005
Hi All,I am using command line bcp utility of SQL Server to import data from a textfile to database.I have some german words in the text file and afterimport the German characters are lost.see eg below.Input : Kühner, Klaus -> Text file valueOutPut: K?hner, Klaus -> Table data, after import.I am using unicode in the commad line.The collation setting is LATINcan anyone show some light.Thanks in advanceManoj.
View 1 Replies
View Related
Mar 12, 2008
I have a strange situation in an Message queue task in SSIS.
I serialize an object in a C# application and add that to an MSMQ as a string. I also ensure that I set the label to "String Message" so that my Message Queue Task can actually receive the message as a String message to variable.
I created an SSIS package that has an Message Queue listener that feeds into a Script task inside a for-each loop.
For each message that I obtain, I invoke a script task that retrieves the value of the variable and then processes this information.
When I enter the entry into the MSMQ, it goes in perfectly fine (since I also tested retrieving this entry from a C# app). However when I use the same logic on the SSIS package using the Script task, I get junk chinese characters.
Has this happened to anyone else?
Any feedback would be great!
Anup
Here is the code for the script task:
mports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim statusMessage As String
statusMessage = CType(ReadVariable("ReviewerHealthXmlMessage"), String)
System.Windows.Forms.MessageBox.Show(statusMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
End Class
View 1 Replies
View Related
Dec 29, 2006
Hi,
I would appreciate any thoughts/ideas on the following use case for the distributed service broker application we plan to migrate from our existing proprietary tcp based message protocol using database tables for reliability.
There are two ssb services running in separate sql server instances, each on a different server machine. For simplicity, let us assume the ssb endpoint names are SSBA, SSBB. SSBB is the Initiator of the Dialog while SSBA is the Target. Now the requirement is that if the underlying network communication between the two ssb endpoints(SSBA and SSBB) is broken or if the critical service SSBB is down, then processing of any incoming message into SSBA's queue from a third service broker service (say SSBEXPR) running within a SqlExpress instance should be delayed until SSBB is alive and network communication between SSBA and SSBB is established. In our existing implementation (wherein SSBA, SSBB and SSBEXPR are windows services) we use a combination of TCP socket disconnects and Heartbeat messages between SSBA and SSBB to determine the health of network connection and that of the SSBB service.
Now my understanding of how the underlying network connection for a ssb dialog works is that if there is no activity on a dialog for a certain amount of time then the underlying network connection is closed. Is there a way to specify the amount of time to say infinite value or something and thus change this behavior? My other question is how can one query the underlying network connection (i.e. a row from sys.dm_broker_connections) associated with a particular conversation? If none of this is possible, then any other patterns/ideas/approach is welcome.
Thanks,
View 8 Replies
View Related
Feb 6, 2007
Hi,
Using the Microsoft Patterns and Practices "Object Builder" (Dependency Injection/Builder library), I wrote an SQL CLR Stored Procedure (using VS 2005 Professional).
All compiles and deploys ok (to SQL Server 2005 Express).
However, at run-time, I get the following error upon a "BuilderContext" object's instantiation: {"Attempted to perform an operation that was forbidden by the CLR host."}
Thoughts on how to get ObjectBuilder working in the SQLCLR?
Thanks!
Andy
(posted below is the sample code...with the run-time exception occuring on the ...BuilderContext cxt = new ... call)
Microsoft.Practices.ObjectBuilder.BuilderStrategyChain chain =
new Microsoft.Practices.ObjectBuilder.BuilderStrategyChain();
chain.Add(new Microsoft.Practices.ObjectBuilder.CreationStrategy());
Microsoft.Practices.ObjectBuilder.Locator locator1 =
new Microsoft.Practices.ObjectBuilder.Locator(null);
// Get error when new'ing BuilderContext: "Attempted to perform an operation that was forbidden by the CLR host."
Microsoft.Practices.ObjectBuilder.BuilderContext cxt =
new Microsoft.Practices.ObjectBuilder.BuilderContext(chain, locator1, null);
View 1 Replies
View Related
Jul 19, 2013
I need to select last order for each employees for homework. I use northwind database for testing. I can solve it by correlated subquery but the professor said me it is not optimized.
select orderid, customerid, employeeid, orderdate
from orders as o1
where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid);
View 6 Replies
View Related
Aug 15, 2012
I have a database that has dozens of tables. Many of these tables reference the employee ID.For example tblDaysOff has a column employeeID that is matched on tblEmployees.ID, and there are many such tables.
Now the employee IDs are changing the way they are generated. Instead of a alphanumeric value being stored as a text value, all employee IDs will be uniqueidentifiers stored as text values.The question is, how can I change every instance of "somevalue" in every record in every column where the column name is "employeeID" in every table in the database to "differentvalue" where employeeID = "somevalue"?This is what I have cobbled together from multiple sources ... but there is a syntax error where @max is located.
Code:
USE CsDB
DECLARE @t TABLE(tRow int identity(1, 1), tSchemaName nvarchar(max), tTableName nvarchar(max))
INSERT INTO @t
SELECT SCHEMA_NAME(schema_id), t.name
FROM sys.tables AS t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name LIKE '%employeeID%'
[code]...
Obviously I don't want to run this and then have to try and recover the database when things go away.
View 14 Replies
View Related
Nov 8, 2014
I have a table Item_used like this
Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5
All I want is to make a report that sum all the quantity of the same items with the same employee like this
Itemname Employee Quantity
pencil samlopez 15
View 1 Replies
View Related
Jan 31, 2008
I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed.
I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.
Any ideas on an intelligent/simple way to do this
View 1 Replies
View Related