T-SQL (SS2K8) :: Returning MAX (Value) In String
Jul 29, 2014
I need to create a TSQL to return MAX(Value) removing the first part and last part.
Example
I have these Varchar Reference code:
1.00001-Q1
2.00100-Q2
3.00005-Q4
I need to cut the string to find the max number excluding (Part1):
1.
2.
3.
And also excluding (Part3):
-Q1
-Q2
-Q4
In this case converting varchar to INT, the correct value that i want is: 00101
The middle part excluding (Part1 and Part3)
Then my final reference could be:
1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4
View 3 Replies
ADVERTISEMENT
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Jul 10, 2014
Here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
View 9 Replies
View Related
Aug 6, 2015
I need to build a query that can return only documents where the field "u_DIM4" for the same document have more than one different value..my script are this one just to having an example:
Select docnome [documentname], adoc [docnr], count(*) [countAlldifferentbyDoc], u_dim4
from fn
where u_dim4 <> '' and data between '2015-01-01' and '2015-07-31'
AND adoc = '02634'
Group by docnome,adoc,u_dim4
ORDER BY 2 asc
[code]...
View 4 Replies
View Related
Jul 3, 2014
Actually title should be returns true when should false.
I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:
IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END
The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.
View 9 Replies
View Related
May 13, 2015
I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:
select a.*
from myTable A
The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.
The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.
View 5 Replies
View Related
Mar 11, 2015
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
View 6 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
Jun 3, 2014
I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.
Perhaps using the datediff() function, or other functions as required to achieve what I need?
View 3 Replies
View Related
Dec 2, 2014
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
View 9 Replies
View Related
Nov 19, 2014
I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)
Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,
[Code] .....
View 9 Replies
View Related
Sep 8, 2006
I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:
select
@error = @@error,
@row_count = @@rowcount
set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'
return @outgoingXML
where @outgoingXML is an output parameter. And I have tried:
set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'
return @serilializedReturnCode
where @serilializedReturnCode is locally declared as varchar(250)
This is the error I get:
Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.
View 15 Replies
View Related
Jul 23, 2005
I need to write a query that tells me which string values are empty orblank in a table.Is it possible to return the length of the string contained in characterfield?
View 3 Replies
View Related
Apr 24, 2008
I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?
Thank you for any help.
View 4 Replies
View Related
Jul 13, 2004
I need to know how to figure out the number of times a character occurs within a string, in SQL.
For example, "happy" concerning "p" would be evaluated to 2.
View 2 Replies
View Related
May 15, 2008
I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
Here is the relevant code-behind:
TextBox tb = (TextBox)formView.FindControl("SpouseName");
tb.Text = e.Item.Cells[4].Text;
DatePicker dp = (DatePicker)formView.FindControl("SpouseDateOfBirth");
if (e.Item.Cells[5].Text.Length > 0)
{
try // this is a try/catch because nbsp is not parsed as a date
{
dp.DateValue = DateTime.Parse(e.Item.Cells[5].Text);
}
catch
{
dp.txtDate.Text = "";
}
}
tb = (TextBox)formView.FindControl("SpouseSocialSecurityNumber");
tb.Text = e.Item.Cells.Text;
Thoughts?
Todd
View 4 Replies
View Related
Mar 18, 2014
How do you take a string and search for the first "-" and then drop everything from the start to, and including, the "-"?
View 5 Replies
View Related
May 18, 2015
I have a table Alert_Types with these fields :
alert_id,
alert_source,
body,
Now whenever a new alert is registered record goes in table like
alert_id = 1,
alert_source= document_detail_view,
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.the converted string was then sent forward using a webservice.now my requirement is to add conditional values in body field e.g:
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
IF isnull(@rate, 0) > 0 Rate: @rate
IF isnull(@rate, 0) > 0 Amount: @amount
how can i parse strings like this. I'm open to change format of values for body field.
View 9 Replies
View Related
Aug 20, 2014
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result.
2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS (
SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL
SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL
SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL
SELECT 'more_stuff.yoursminehershis_falafel', 'yours first'
)
SELECT string, pattern, ca.item, ca.itemnumber
FROM testctes
CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
View 9 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Oct 27, 2014
I am having a
Source Table: #test1(studID, FIrstNAme,LasteName)
Target Table: #Test2(StudID, UserName)
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar
2, Arun, Pawar
3, Ashis, Panday
Then i want to create username like:
apawar
apawar01
apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.
View 5 Replies
View Related
Nov 19, 2014
I need to add a filter clause like
WHERE username = '%%%'
I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.
View 6 Replies
View Related
Jan 22, 2015
i have 2 table:
t1:
t1_idint
namenvarchar(10)
t2:
t2_idint
t1_idint
Sematnvarchar(50)
then i write following SQL Command
SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product
FROM dbo.T1 INNER JOIN
dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id
GROUP BY dbo.T1.t1_id
but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.
View 3 Replies
View Related
Mar 27, 2015
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
View 8 Replies
View Related
Jun 30, 2015
I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.
User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD
13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD
more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...
View 3 Replies
View Related
Sep 4, 2015
I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.
TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'
From the above text1, I want to get “AXNARENDR”.
Based on logic defined below:
First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”
In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string
i.e AXNARENDR (TEXT1 I defined “A” with 4th occur
TEXT 2:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'
From the above text2, I want to get “AYENDGHR”.
TEXT 3:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'
From the above text3, I want to get “AZENIVKHR”.
View 9 Replies
View Related
Mar 19, 2014
My sample data is as below.
plan type A change from plan type B from Plan type C
Insurance plan M changed from Insurance plan b From plan d from Plan N
Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.
plan type A change from plan type B
Insurance plan M changed from Insurance plan b
View 3 Replies
View Related
May 2, 2014
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem
HS321 13-14 D Elem
IN636 13-14 C Elem
030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
View 6 Replies
View Related
May 29, 2014
I am trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.
ALTER PROCEDURE [dbo].[Chargeability]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ......
View 9 Replies
View Related
Jun 5, 2014
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[Code] ....
View 9 Replies
View Related
Jul 31, 2014
Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:
7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
View 9 Replies
View Related
Aug 7, 2014
The data we are getting from our shipping company has the customer name and customer number attached.
so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'
So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)
I need to match this up with our customer table... on Customer Name.
View 9 Replies
View Related
Mar 23, 2015
I have data like below
Potter, James J
Williams, Ted R
Allen, Gary G
I want to remove Middle Name from the output
Potter, James
Williams, Ted
Allen, Gary
My Query:
SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
View 3 Replies
View Related
