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?
Someone claimed that the query:
INSERT INTO [table1] SELECT * FROM [table1]
was a result of running DBCC DBREINDEX.I wasn't able to replicate this when I did a trace on my local machine, but SQL Monitor from Red-Gate shows that query happening for at least some of the tables that are being reindexed via a job. Is there anyway to examine the actual code inside DBREINDEX to see exactly what commands it may execute?
The code block is embedded into a grid view item template. <ItemTemplate> <% if ((bool)Eval("isNew")) { %> New Post <% } %></ItemTemplate> but as per rule Eval and Bind can be only use bind the data. How would i pull the information from sqldatasource in the template like this?
View 4 Replies View RelatedHi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
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 got a table with organisation codes with postcal code ranges, from-to.
Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249
I would like to generate rows for this range like this:
001 5200
001 5201
001 5202
.....
001 5249
The table looks like this:
SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation
Dear all,
I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.
I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.
However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.
In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.
Is this possible? If so, could someone please refer me to an example of how to do it.
If it can be done, it opens up lots of very cool possibilities!
Thanks.
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 uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'
order by '+ @SortExpression +''
exec sp_executesql @sql
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
Hi, got a problem and can't figure this one out.
basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...
record 1 : (01)
record 2 : (02)
etc...
and i want to compare this one field to those records to see whether that text string contains anything the table contains.
i've tried ContainsTable & IN but still can't figure this out.
any help greatly appreciated
:o
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
Hi guys,
I have a large text that can have some ENTER character inside of it. The problem is that the text must be inserted into a txt file in a single row. So what I need is to find those ENTER character and replace them with a space so as to have the whole string in one row.
How can I build that formula in derivide column?
Thanks for your help
Hi all,
Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.
For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.
Can this be acheived through any function.
Cheers !
ash
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
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.
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_
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 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?
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