SQL Server 2012 :: Split String Into Columns Based On Special Character
Dec 4, 2013
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
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment]( [EQU] [VARCHAR](50) NOT NULL, [Notes] [TEXT] NULL, [Facility] [VARCHAR](50) NULL) INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility]) SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA' SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
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.
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
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),
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255) SET @code = '12333345566689'; SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields: select UserName from MyDirectory NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos How can I fix this query so that it returns everything to the right of the '' character? So I want the query to instead return the following: Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
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.
RO1 BOOK RL1 PDF/ECOPY RO2 PAPER RL2 CONFERENCE RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…
See the attachment for better formatting...
--------------------------------------------------------------------------------------------- status location contact --------------------------------------------------------------------------------------------- ONLINE WEB BOB ON PRESS FOR PRINT S.R STREET, LONDON SMITH
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
CREATE TABLE #Names ( ID INT IDENTITY(1,1), NAME VARCHAR(100) ) INSERT INTO #Names VALUES ('S-SQLXX') INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLYY') INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLCL-HR') INSERT INTO #Names VALUES ('S-SQLCL-MIS') SELECT * FROM #Names
--I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.
--I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.
--However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name
--I want ONLY these returned IN the SELECT
SELECT * FROM #Names WHERE ID IN (1,3,5,6) DROP TABLE #Names
I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.
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
Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
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:
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:
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 like to extract all special characters in one table with many fields. How can I handle this without using 'replace' for each field and many characters ?
How can I insert by asp a string containing 'That's the stringAsp code:SQL = "insert into tablename (columnA) values ('" & variable & "')"The problem is when variable contains a ' (single quote), it stops thestring definition and get an error.Can I do something?Thanks--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
I had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:
CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200)) GO INSERT #ABC SELECT 19999,'SQL2000 ' GO SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000' --(0 row(s) affected) SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000' --(0 row(s) affected) select * from #ABC WHERE REPLACE(DESCRIPTION,' ','')='SQL2000' --(1 row(s) affected) GO DROP TABLE #ABC :rolleyes: I believe, ENTER has some value within a field.
shailendra writes "Hi,everbody lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.
I am trying to take the results of a query and re-orient them into separate columns.
select distinct W_SUMMARYDETAILS.FACILITY_ID, W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year) W_SUMMARYDETAILS.FACILITY_NAME, W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year) From W_SUMMARYDETAILS full outer join W_DEF_SUMMARYDETAILS on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
