I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString
1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString
1 Extracts^This^String1
2 Extracts^This^String2
3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
I have a column that has data in column user4 such as :
CALBRIG ALBION COMPANY LTD
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
I have a table PLACE with a character column CODE
[Code] [nchar](4) NULL
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4)
FROM [Place]
WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hi,
I have a situation where I have to write a SQL to generate a sequence number from the count of the group by.
Example:
I have src data :
---------------------------------------------------------------------------------------------------------------
SUBSCRID col1 col2 col 3 col4
1001 val1 val2 val3 val4
1002 vala valb valc valc
1001 ................
1002 ..........
1001 ...........
1003 ................
---------------------------------------------------------------------------------------------------------------
So my result should be :
---------------------------------------------------------------------------------------------------------------
SUBSCRID SUBSCRCNT col1 col2 col3 col4
1001 1 val1 val2 val3 val4
1001 2 ...................
1001 3 .....................
1002 1 vala valb valc valc
1002 2 ................
1003 1 .................
---------------------------------------------------------------------------------------------------------------
Any help on how to write a query will be appreciated?
Thanks,
Venkat
Hello,
I have a table in sql server that has a field with numeric characters, i would like to select some but not all characters from this field and insert it into a new field. The field that i want to get some characters from has other characters that i dont need, It has a lot of zero's next to the numbers that i need. so far i created a stored procedure, but its not working. I want to extract the characters and insert them into a new field in the format "000.00" or "00.00". The values are either hunderths or tenths, which makes it difficult for me to decide which characters to ignore. Please see my SP created so far below:
CREATE PROCEDURE dbo.spUpdatePaymentDetails
AS UPDATE dbo.PaymentDetails
SET NewAmount = SUBSTRING('00' + TransactionAmount, 1, LEN('00' +TransactionAmount) - 2) + '.' + SUBSTRING('00' + TransactionAmount, LEN('00' + TransactionAmount) - 1, 2)
GO
the character "." is not in the original field so therefore i would like to insert it into the new field.
Please help urgently.
Tnx
How would i find the 3rd date in a sequence of dates, that is ordered?
Example
Date1 = 08-01-01
Date2 = 08-02-01
Date3 = 08-03-01
Date4 = 08-04-01
i need to retrieve Date3
I am facing some issues while working on SSIS in VS2005,
The Scenario is we have 35 excel files from which we have to import data
to Sql 2005.
We have to execute these packages in a specific sequence so we are not
using For Each Loop Control.
Now the Problem is this that we have 35 executables as an output that is
corresponding to each package, and from each executable we can Change
excel path and Sql Connection String.
The problem is that one has to open each executable to set the path; we
wanted to keep them Configurable like , as if we can have any variable
define in the packages for path, and in the packages we use that variable
and append the XLS file name to it like ..
@PathVariable+ test.xls
And from the Configuration file at run time we can set the path to this
variable like
@Path Variable="C: /Windows/ Test Folder/"
And then executable picks the path from there.
Is there any possibility to implement any thing similar, we have tried but
its not working, any help in this regard will be really appreciating.
Its really urgent, I will wait for feedback on this.
I have a values a column like this
Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)
So i just only need 07:00-16:30 this kind of part from above all these string in sql server.
I need to split a string in two if there are lowercase characters atthe end of it.For example:'AAPLpr' becomes 'AAPL' and 'pr''Ta' becomes 'T' and 'a''MSFT' becomes 'MSFT' and '''TAPA' becomes 'TAPA' and ''I am using SQL 2000. I read about "collate Latin1_General_CS_AS" butnot sure if I can use that outside of a select statement.Thank you in advance for any help.
View 2 Replies View RelatedHere’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
RegionalID pbxID userID
0 1012 17
0 543 2
0 10961 6
0 16499 26
0 14061 36
0 15882 2
4 15101 6
4 15101 26
6 16499 2
6 16012 26
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup
838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554
5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554
12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554
10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554
13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554
19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554
19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554
15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249
3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249
32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc
79731 PRETORIA 0123620NORTH EASTERN REGION
79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION
79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION
79761 PRETORIA 01730140NORTH EASTERN REGION
79771 PRETORIA 01230150NORTH EASTERN REGION
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaIDRegionalIDDongleAreaCodeAreaDescUpdateStatus
121UAOumashoop0
131UBPietersburg0
141UCWarmbad01
151UDNylstroom0
161UEPotgietersrus0
271UFLouis Trichardt0
281UGMessina0
291UHEllisras0
301UIThabazimbi0
311UJPhalaborwa0
321UKTzaneen0
331UTStanderton0
341UMMeyerton0
351UNNelspruit0
361UOWitrivier0
371UPLydenburg0
381UQMiddelburg0
391URWitbank0
401USBronkhorstspruit0
461UZOlifantsfontein0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialupRegionUpdateStatusRegionCodeLocalRegion
10800003554North Eastern010
20800005027Gauteng020
30800006194Eastern030
40800004249Central040
50800201859Southern050
60800201989Western060
70800113515HO101
80800222204Tellumat070
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.
My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.
The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.
So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.
I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.
I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.
How do I count the number of specific characters in a string ?
Example:
declare @var as varchar(50)
set @var="1abc1efg1"
If I wanted to count the "1"...I'll get "3" for answer.
This could maybe done by using a while loop, but is there any T-SQL command for this?????
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
Please find the necessary SQL to create the table with sample data down below.In this simple table, I'm keeping project status logs i.e. what project, what happened and when.I want to create a select statement that will give me the ProjectId and the time stamps from "Contract Signed" to "First Meeting". So, I'd like my data to look like this:
ProjectId - FromStatus - FromTimeStamp - ToStatus - ToTimeStamp
123 - 'Contract Signed' - 6/21/2015 3:33PM - 'First Meeting' - 6/27/2015 8:45AM
456 - 'Contract Signed' - 6/25/2015 9:00AM - 'First Meeting' - 6/29/2015 2:50PM
987 - 'Contract Signed' - 9/29/2015 11:00AM - 'First Meeting' - 7/11/2015 10:00AM
Please notice that in my SELECT statement, I don't want to see anything else. For example, I don't want ProjectId: 555 included because it never made it to "First Meeting". I also don't want to see any information about "Press Release" for ProjectId: 123. I only want to see those projects that went from "Contract Signed" to "First Meeting".
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProjectStatus](
[LogId] [int] NOT NULL,
[code]...
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
View 14 Replies View RelatedI have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
i have a table whose Primary Key is "UserID". the sample "UserID" are M1,M2,M3,M4,B1,B2,B3 .
i want that when i insert a valuse "M4" in the table ,by pressing Submit Button.
it should not be at the end or at the start of table.
Rather it should be next to M3. like the following
M1
M2
M3
M4
M5
B1
B2
B3
i need the C# code of how to do that !!!!
Thanks
I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.
<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
View 4 Replies View RelatedI am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events).I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
View 4 Replies View Related Hi, I have a table with one XML type column. This column holds custom field information. Its used as a way of storing ad hoc fields and data that don't fit the DB design. <?xml version="1.0"?><contact><Reference>A39390TFH</Reference><Misc>all kinds of stuff go in here</Misc></contact>I want to provide a way of displaying the data stored in this column in the same DataTable as normal relational data from the same table. I have been able to achieve this goal BUT I want to know if the community had any ideas on how I could speed the process. I am using the XML value() function. It allows me to extract the data I need. SELECT Name, Number, Reference FROM
(
SELECT Name, Number, xmlvalues.value( ' (contact/Reference)[1] ', ' varchar(40) ' ) as Reference
From MyTable
WHERE Name = 'Some Dude'
AND xmlvalues is not null
) T
GROUP BY Name, Number, Reference anyone know if there are better xml functions to get this data out of the XMLColumn?????? There is no schema, because each xml fragment has different tags and different values. Regards Niall
Using SQL2K.
I'm extracting columns from a CSV file.
Once column, in the CSV file, has a format of general and is only populated by numbers (0 - 126); no decimal places.
During the extract, the column is copied into my staging table as an NVARCHAR value. However, after the extract, the values have all been appended with a ".0". What could be causing this?
Thanks!
i have dat like this
ab0001
a001
abc001
I need the following output
ab
a
abc
Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1 CAN
2 2
3 1,2
4 4
5 1,2,3,4,5,CAN
6 10
7 CAN
8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
Hi!
I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:
Dim insertSQLDatasource As New SqlDataSource()
insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString
insertSQLDatasource.InsertCommandType = SqlDataSourceCommandType.Text
insertSQLDatasource.InsertCommand = "INSERT INTO Demo_Theatre_DB (StartTime, EndTime, CompanyName, Purpose, AccountManager, Presenter, ColorCode, Status, Comments) VALUES (@StartTime, @EndTime, @CompanyName, @Purpose, @AccountManager, @Presenter, @ColorCode, @Status, @Comments)"
insertSQLDatasource.InsertParameters.Add("StartTime", StartTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("EndTime", EndTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("CompanyName", CompanyNameTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Purpose", PurposeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("AccountManager", AccountManagerTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Presenter", PresenterTextBox.Text)
insertSQLDatasource.InsertParameters.Add("ColorCode", ColorDDL.SelectedValue.ToString)
insertSQLDatasource.InsertParameters.Add("Status", StatusRadioButtonList.SelectedValue)
insertSQLDatasource.InsertParameters.Add("Comments", CommentTextBox.Text)
Try
insertSQLDatasource.Insert()
Catch ex As Exception
Panel1.Visible = True
InsertMsgLabel.Text = ex.Message.ToString
Finally
insertSQLDatasource = Nothing
End Try
Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.
Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString")
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = com.ExecuteReader()
While r.Read()
StartTimeTextBox.Text = r("StartTime")
EndTimeTextBox.Text = r("EndTime")
CompanyNameTextBox.Text = r("CompanyName")
PurposeTextBox.Text = r("Purpose")
AccountManagerTextBox.Text = r("AccountManager")
PresenterTextBox.Text = r("Presenter")
ColorDDL.SelectedValue = r("ColorCode")
StatusRadioButtonList.SelectedValue = r("Status")
CommentTextBox.Text = r("Comments")
End While
I hope someone can help me with this.
Your help will be appreciated.
Thanks!
Hi,
I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?