Can it be possible to split the below string to NICK and SERVERS. Query which provides two columns, first column data should be NICK and other SERVERS.
_BF-TYPE:NICK_ _BF-SUBSYSTEM:SERVERS_
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.
I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.
Is there a way I can extract the values from the string into two separate columns?
XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c
I would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies View RelatedSo I would like to split rows in my query based on if they match criteria and I really havent dealt with splitting in SQL only combining(which is usually what everybody does lol)
So for instance my query results is this
SOPNUMBER TAX TOTAL
14-123456 $500.00
I want to display full name column as FirstName and LastName.
Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav
i.e. Before Space is FirstName and After Space is LastName.
Using SQL Server 2008 R2
I'm trying to create a report and chart for a a manufacturing resource's activity for a given period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- Create tables to work with / Source and Destination
CREATE TABLE #Jobs
(
ResourceID int
,JobNo VARCHAR(10)
,startdate SMALLDATETIME
,enddate SMALLDATETIME
[Code] ....
Below is some sample data
|--------------------------J1------------------------------------| running
|----D1-----| |-------D2-------| down
|--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running
|----D1-------| down
|-----------------J1-----------------------| |----D1-------| result
I have a column with the following information in it. I need to split the column and parse the values. I have Parsed one single values from but, not multiple times.
<TV<MR1#4.0#true#2.0#USD>VT>,<TV<MR2#3.0#true#1.5#USD>VT>,<TV<MR3#0.0#true#0.0#USD>VT>,<TV<MR4#0.375#true#0.19#USD>VT>
MR1 -Model Code,
4.0 - Percentage,
true - isAbs,
2.0 - AppliedValue --> This is Actual amount applied as tax.
USD - Currency
For the example provide , the totalTaxValue would be 2.0 + 1.5+0.0+0.19 = 3.69
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table
tbl_1
(txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
[Code] ....
I have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE
Looking for split function that accepts a multiple character value as a delimiter?
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend.
Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns.
Thanks
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it
select a name for each value and return a recordset. Any pointers greatfully received.
First attempt is dreadfully slow as I am opening recordsets each time
Function func_getFood()
Dim rsfoodsql
Dim foodoutput
for x=1 to ubound(masterfoodarray)-2
set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'")
if not rsfoodsql.eof then
foodoutput=rsfoodsql("foodname")
if not foodoutput="" then
response.write foodoutput&"<BR>"
end if
end if
next
End Function
Hope someone can help,
cheers
Hi
I have this string which might have a hyphen in it "-"
What I want to do is
if I get a hyphen then take all characters after hyphen
else take only all the characters starting from the 5th position of the string
How can this be achieved?
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query
any one know help me
Regards,
Prabu R
I have data like 'US.USA.Florida.Miami'. I need to split this string as below
Continent Country State Location
=========== ========= ========= ============
US USA Florida Miami
Delimiter is dot '.'.
Is there any chance to do this using the sql server built in functions.
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.
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont.
My code below:
Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''")
Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()
hi,
i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines",
the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less)
now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter.
e.g
@item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10"
if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null.
any response will be appreciated. thanks.
hi all, i am still a newbie in sql
i got a problem with my query
for example:
-------------------------------
DECLARE @LongString VARCHAR(10)
@LongString = "Hello-Word"
------------------------
i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql
Thanks a lot guys
arifliminto86
I have a string
@string = 'abc,def,ghi'
Now I have to display
abc
def
ghi
I have to separate a comma in that
Is there an function like split in sql
plz help me
Thanks in Advance
Suresh Kumar
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View 5 Replies View Related
I have a table called products with the values like
ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J
I need to display each productid's with
ProductId ProductName
10 A
20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J
I will be appreciated if you can send me the code.
Thanks,
Mears
Hi,
I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'
I need to split the above string and get each of those values... ' / ' delimiter
Can some one please help on this.
Thanks
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW
GO
SELECT
vPerf.DateTime,
vPerf.SampleCount,
cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU,
vPerformanceRuleInstance.InstanceName,
[code]....
I have a string stored in field v_images in table tbl_DealerFeed_temp. This string is split by the "|" character. I want to create a SQL query to grab this string, split it, and insert it into tbl_Vehicles_photos. I also need to pass the value d_id into the photos table:
tbl_Vehicles_photos:
vp_id, d_id, vp_image_name
The vp_id is the unique ID. I need to populate the d_id and vp_image_name field with my values.
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) -1, -1)) AS [FirstName],
RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName))) AS [LastName]
This is only gives first and last not first and middle
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT
STUFF(@FullName,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+5,LEN(@FullName),'') [Firstname1],
STUFF(@FullName,1,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+4,'') Lastname1
Not right as it gives
Vauxhall
Adam Rocks AIR Vauxhall
Ideally the result should be
Vauxhall
Adam Rocks AIR
I am performing analysis of linked servers across 2000-2008R2 and need to find/build a list of linked servers that are truly active. For the sake of the post let's define 'active' have executed a distributed query in the last 5 days.
I have been scanning the DMVs without much success. Perhaps I must look more closely at MSDTC?
The end result would be to cleanup 300+ linked servers across 40+ SQL Servers.
I have a string which is delimited by commas:
Green,Red,Blue,Orange,Black,White
I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).
For instance:
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...
I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.
I am using SQL Server 2008. I have strings like this:
AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)
I want to split the string with delimiter '-' and store as first part and second part.
I saw few sample functions to split a string but these return table of values.
I simply want first part and second part.
In above examples context:
@First_Part = 'AB'
@Second_Part = '123'
Any simple way to do this?
I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.
For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.
Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?
I have a RPC which gives me multiple - single record rows like
Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column
Now I need to split the above record into 6 Fields and populate in my local DB
I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's
Any ideas / directions?
I am passing in a string of dates, delimited by a comma.
So
01/01/04, 02/01/04, 03/01/04
etc
I would like to enter each of these values into a table via an INSERT stored procedure.
Does anyone have any code for this?
How I can split the half hour in number from this string?
20130329070000 (it's varchar)
Easy for the date
cast (left(20130329070000,8)as date)
I can achieve even the Half Hour
select
left(SUBSTRING('20130329070000',9,4),2)+':'+RIGHT(SUBSTRING('20130329070000',9,4),2)
But what I need is the Half Hour in numeric format, so 00:00
should be 1, 00:30 should be 22, 01:00
should be 3, 01.30 should be 4......23:30 should be 24.
I already did but I can't find the piece of code I used.