I have a string ,want to split the values after every space as column value and insert them into a table
1306453 0 0 0 0 0
col1 col2 col3 col4 col5 col6
1306453 0 0 0 0 0
I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.
Old Table:
Code:
ID, StringValue
New Table:
Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.
Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable
Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?
Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1
[code]....
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 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 would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
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
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
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
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 trying to spilt records into days by the start - End datetime.
I would send an image and data but because I am new to the forum, I am blocked sending images.
"Body text cannot contain images or links until we are able to verify your account"
How I can forward an image.
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.
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
we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
I have a field in a table that contains addresses e.g
15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road
I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.
So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.
I have values in two columns separated by commas, like shown below:
I need the Output Like
How to do this in SQL server ?
Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest
Results:
IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
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]....
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.
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I have a string with values value1,value2, value3, value(n) which I would like to append to my table.
In the second column of my table I have a parameter which stays the same, so I end up with (if the parameter value is "123456")
Column 1 | Column 2
123456 | Value1
123456 | Value2
123456 | Value3
I would like to set up a single SQL statement which will process this regardless of the number of values (therefore rows) desired.
Something like:
INSERT INTO dbo_tblUserLevelApplicationRequests ( Column1, Column2) select EmployeeNumberParam as EmployeeNumber, ((stringofvalues) as valuestring)
Is it possible to do this with a single SQL statement?
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision
Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative
Expected
Customer 1 had a max of refnum of 2 and 1st one on number
IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative
Currently we have requirement like below.
Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"
In the DB i have column values like below.
FileType Val
FILENAMETYPE Direct
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
I have a table name tbl_testme with columns (id,mac,keys,outputmk)
mac column have 12 character and keys have 16 character
mac keys
6545da7n9hg8 hsi457s5sd77jk87
What i want is i need to split the column into 4 characters of both column E.G.
(6545 da7n 9hg8) and (hsi4 57s5 sd77 jk87)
after splitting i need to take last 4 character of key(jk87) and last 4 character of mac(9hg8)and join them and insert that into ouputmk column.
E.G.
(jk87-9hg8-sd77-da7n-57s5-6545-hsi4)
I need this to insert in outputmk column ....
I am able to get a list of columns in a table from the query I have written shown below:
select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name
But I am looking for the resultset with the format below:
TableName Columns
employee employeeid,employeename,employeesalary
order orderid,address,price
How to add columns to temporary table in loop with sample code.
View 12 Replies View RelatedI have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000
I would like to create a function that will replace a string using a reference table
I have a table : reference
ID String ReplaceWith
1 ≈ &
2 < <
3 > >
If I pass a string into a function, function needs to replace a string with replace with string column in reference table
For example, if I pass a string : car $ap; fjld
The function should return car & fjld
How can i create a function like this so that i can call it in Stored procedure....
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0