How To Split The Field In The Sql Query?
Mar 23, 2007
I have the field LocationID (string)which has values like
"AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers,
I want to split it like "AZ" ," 001"
"AZ","002"
"IN" "002"
"IN" "004"
now i will populate dropdownlist with unique values of "AZ" "IN"
according to first dropdownlist how i will populate second dropdownlist?
So how to write sql query for splitting? and then populating the dropdownlist ?
View 4 Replies
ADVERTISEMENT
Oct 9, 2001
I have a table with column person_name and I want to update that table and
insert the values of different columns FIRST_NAME and LAST_NAME.
Anybody can tell me the T-SQL code to do above problem?
Thank you very much!
View 3 Replies
View Related
Jul 23, 2005
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View 4 Replies
View Related
Apr 14, 2006
I have the following fields in table A:
GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI
===============================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
===============================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
View 4 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI
===============================================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
=====================================================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit - Total Debit in Period 01
Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
View 2 Replies
View Related
Jun 7, 2002
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.
Any ideas how I could do this in T-SQL?
View 9 Replies
View Related
Nov 9, 2005
Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.
View 5 Replies
View Related
Aug 3, 2006
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start?
lets say i have "12 north plaza boulevard apt.16" and i want it to become:
address_number
12
suffixA
North
street_name
plaza blvd
suffixb
apt16
View 1 Replies
View Related
Nov 27, 2007
HiHo,
just a beginners question:
I have the following row with 2 fields:
Field 1: Task A
Field 2:´ 1;2;3;4
The number of semicolon divided elements in Field 2 is variabel.
I would like to create new rows like:
Row 1 Field 1: A Field 2: 1
Row 2 Field 1: A Field 2: 2
Row 3 Field 1: A Field 2: 3
Row 4 Field 1: A Field 2: 4
I think I should use a Foreach Loop.
But I don't exactly how to do it?
best regards
Chris
View 5 Replies
View Related
Jun 21, 2007
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually.
Thanks
View 8 Replies
View Related
Aug 13, 2004
Hi.
I'm trying to write an SQL Query that will take a delimited field and return each item as a row.
Example
Take the AuthorizedRoles and TabID fields from the Tabs table
AuthorizedRoles TabID
0;11;__________1
0; 15 ;17;______6
-2;____________7
I would like to return a unique record for each Authorized Role
AuthorizedRole TabID
0____________1
11___________1
0____________6
15___________6
17___________6
-2___________7
Any ideas?
Cheers
Dave
View 2 Replies
View Related
Sep 24, 2012
In SQL SErver 2008, I have a text column. I need to display either 2nd word in the text column or 1st word in the text column based on certain conditions.
How shall i display either 2nd word or 1st word from a text field.
View 1 Replies
View Related
May 15, 2015
I need to split a field based off it's length. Â Initial thought was to take Left(field, characters), and Right(field, characters), but now I see I am duplicating the data as I am capturing too much of the data in. Â However, what I actually need to achieve is in field1 capture the left 1000 characters, and in field 2 take from character 1001 to the end. Â How can I update my syntax so that field2 will ONLY capture from character 1001 to end?
field1 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN LEFT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) ELSE exitinterviewnotes END,''),
field2 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN RIGHT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) END,'')
View 5 Replies
View Related
Aug 12, 2015
I have a database field which consists of a long string of values with some separator.
Ex:Â Injection^!@$#Medication
in my report i have to split the string as
Injection
Medication
till here i am able to display the results but in addition to that i have to display check box against these values like below
and this string can contain any no of values ex: if we have 4 values are separated within a string then i have to display 4 check boxes against 4 results.
View 6 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Jun 29, 2005
:confused:
Hello
i have some problem with this MDX Statment :
with
SET [kunden] as 'Filter (descendants([Dim_x].[Alle Kunden], , LEAVES),
left([Dim_x],1,1) = "(" ) '
select
{descendants([Dim_Kx].[Alle Kunden], , LEAVES) } on rows,
{([measures].[Standard])} on columns
from z
Result :
Standard
(test1) 44444
(test2) 54567
(test3) 3214
test5 4535
test6 11111
but i want to filter i need only result with () how can i do it please
i need help
many thanks,
Prince
View 1 Replies
View Related
Sep 23, 2007
Hi,
I have the following table in MsAccess
EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
Thanks,
ron
View 8 Replies
View Related
Jan 22, 2008
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1 Col2
BMW FordMercedes Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View 1 Replies
View Related
Aug 3, 2007
How can i write a query to split a database column and shows 2 new columns. In my database column
I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter
and run another query.
For example a database column with average number and range number.
Thanks
Daniel
View 16 Replies
View Related
Mar 21, 2007
Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.
Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'
Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918
Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...
View 7 Replies
View Related
Oct 19, 2015
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),
[code]....
View 8 Replies
View Related
Aug 27, 2014
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
View 1 Replies
View Related
Mar 15, 2008
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View 2 Replies
View Related
Mar 9, 2004
Hi Friends,
I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL??
ID Phone Name
1 122 abc
2 xyz
3 444 mmm
4 525 ccc
5 nvb
Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help
Ashu
View 1 Replies
View Related
May 30, 2006
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View 17 Replies
View Related
Jun 25, 2004
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example:
SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total
Obviously that doesn't work, but what could I do to get that basic thing to work?
Thanks!
View 4 Replies
View Related
Sep 14, 2006
I want to kown if a field is an identity (counter) using a query or a stored procedure ?
Thanks
View 1 Replies
View Related
Nov 5, 2007
I have written this query in SQL
select * from wce_contact
where (Mail3Date <= '2007-09-25')
AND NOT (Mail1Date is NULL )
AND NOT (Mail2Date is NULL )
AND (Mail4Date is NULL )
AND (Mail5Date is NULL )
AND (Mail6Date is NULL )
AND (Mail7Date is NULL )
AND (Mail8Date is NULL )
AND (Mail9Date is NULL )
AND (Mail10Date is NULL )
AND (Mail11Date is NULL )
AND (Mail12Date is NULL )
AND (Mail13Date is NULL )
AND (Mail14Date is NULL )
AND (Mail15Date is NULL )
AND (Mail16Date is NULL)
Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days.
I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query
AND (IDstatus is Null)
But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results.
I was wondering if anybody could possibly help me?
Thanks!
View 20 Replies
View Related
Aug 2, 2007
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:
Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,....
From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID
Where Order.OrderID=10
so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...."
How can I write this query in T-SQL?
Thanks
View 1 Replies
View Related
Dec 2, 2015
How to use Xquery to get the success value and the result value from the following XML.
This XML is stored in a SQL Table XML field, i want to be able to get all the success and reslut values for all the records:
Here is my XML
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:ns1="https://data.abc.biz/mysoappathtest/?soap:customcalls:v1"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
 Â
[Code] ....
I am trying to get the result = 15810 and the Success value = true
View 3 Replies
View Related
Nov 25, 2007
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits)
Query: Select * From table1
field
------
1
2
3
5
6
8
9
Expected query:??
filed1
------
4
7
Please help me to get a query which will provide my expected data.
Thanks.
/Fakhrul(mfhossain@gmail.com)
View 4 Replies
View Related
Feb 28, 2007
Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
View 2 Replies
View Related
Aug 3, 2007
I want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View 4 Replies
View Related
