Hi there I have this following data: 0297144600-4799 0297485500-5599 The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach. But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid: 029714460...
I just creates this stored procedure in selecting palletnumber=datatype is int. SerialNumber is varchar. Then pallet numbers are 1 - 200..But if i enter 2 it shows 2 with other palletnumbers. I change @search to nchar and its ok but serialnumber is has error. Im new in stored procedure. ALTER PROCEDURE [dbo].[sp_SearchFresh] -- Add ...
Hi there I've to manage the data into this and it has blank space in between: 0297144600-4799 0297485500-5599 0297144600-0297144799 0297485500-5599 0297144600-0297144799 0297485500-0297485599 I want to normalise the first/2nd row like the last row which means 0297144600-4799 >> 0297144600-0297144799 The challenge is that it can ...
What would be the simplest way to create an exact structural replica for Table1 and all its indexes in the same database. Let's say Table1Dupl where all constraints/indexes from Table1 with suffix Dupl. I understand that I may do manual renaming "script table as " "create to" from Management Studio, but looking for fully programmatic w...
How can I get the last or next Identity of a table? ...
Hi, I am trying to decipher some SQL statements using, SQL Profiler, that are run from a proprietary application. One of the statements is: SELECT ID, Groups, Name, Gallery FROM DashboardReports WHERE (Groups & 0x800) <> 0 AND Root = 1 ORDER BY Name Can anyone explain how the WHERE clause works? I've never seen a WHERE clause like ...
Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field. EmployeeName ------------ Anand Anand Anil Dipak Anil Dipak Dipak Anil Using one query, I want to delete the records which are repeated. How can this be done with TSQL in SQL Server? ...
Hi, I'm trying to write this query, that would calculate the average value of all the columns except the one that contains the type value, which I'm grouping the whole query by. So for 4 types for example, each column in the resulting table will contain the average of all the other three type's values, i need to exclude the current type...
I am using the following query to determine missing indexes: select db_name(d.database_id) as DatabaseName, object_name(d.object_id) TableName, d.index_handle as IndexHandle, d.equality_columns as EqualityColumns, d.inequality_columns as InequalityColumns, d.included_columns as IncludedColumns, d.statem...
Hello All, I have a table with unique values within it and once a stored procedure is called, I use the following code within a sub-query to get a random value from the table: SELECT TOP 1 UniqueID FROM UniqueValues WHERE InitiatingID is NULL ORDER BY NewID() ASC I have however noticed that I am managing now and then (and I'm guessin...
ALTER PROCEDURE [Lending].[uspHMDALarIncomeGet] (@ApplicationId int) AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Total Income Data DECLARE @ApplicantId int = (SELECT AT.ApplicantId FROM Lending.Applicant AT WHERE AT.ApplicationId = @ApplicationId) SELECT I.Amount FROM Lending....
I've got the following piece of SQL Code that is giving me a titular error. WHERE (SELECT Tokens FROM StringSplitter(@DocumentValue, '|', 1)) IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)) Where @DocumentValue and @sortValue are both concatenated strings separated by a delimiter (in this case, a '|'). The...
I need to write a procedure that will allow me to select x amount of rows and at the same time update those rows so the calling application will know those records are locked and in use. I have a column in the table named "locked". The next time the procedure is called it will only pull the next x amount of records that do not have the "...
I got 3 server. development (my local development machine) Test purpose (Beta site). Production I got Instances in both SQL server 2005 and 2008. So, When I want updated data, i take the latest backup from our production server and restore it on the Test or development Server. I got a script that make the changes (restore the file ...
Hi folks, i have a list of alphanumeric tokens, say '1a', '1b', '02', '03', '10', '11', etc... Now, what's the best way to do an order by on this list of tokens? I am getting '1a', '1b', '10', '11', '02', '03', but i need it to be '1a', '1b', '02', '03', '10', '11' UPDATE ok, i am doing this after the suggestion but it's not w...
int id = 1; string chain = "("; SqlDataReader dr = SqlHelper.ExecuteReader(string.Format("SELECT a.Id as x, c.Id as y From Friends b INNER JOIN Users a ON b.SenderId = a.Id INNER JOIN Users c ON b.ReceiverId = c.Id WHERE (c.Id = {0} OR a.Id = {0}) AND State = '{1}'", id, "ok")); if (dr.HasRows) ...
Hi I am trying to get the the result of in time and out time from dates but it returns only hours using following select Query as follows SELECT DATEDIFF(Hh,InTime,OutTime) as Diff_time from EmpLogTable and i need result in HH:MM Suppose my in time is 11 am and out is 5.49pm so o/p would be 6.49 but using above select query i ...
Assume I have some stored procedure (and I can't change it) which is returning a result set: create procedure test_procedure as begin select 1 end I know that I can insert result set into table, so it would be hidden to the calling code: declare @t table(i int) insert into @t exec test_procedure Are there any other ways to h...
select replace(stuff('123456',2,2,'ABCD'),'1',' ') select LEFT('ABCD456',4) select left(replace(stuff('123456',2,2,'ABCD'),'1',' '),4) Ok now the first select outputs 'ABCD456', the series of functions evaluates to that exactly the first parameter to the left function in the second select second select returns 'ABCD' as expected thir...
I have these information : Table "Users" => **Id** **Name** 1 a 2 b 3 c 4 d 5 e Table "Friends" => **SenderId** **ReceiverId** **State** 1 2 x 2 3 ok 3 1 ok 3...