Data Warehousing :: Query To Extract All Text After Hyphen From String Containing Comma Separated Text
Aug 27, 2015
I have a parameter value as shown below and this is dynamic and can growÂ
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA Â --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL Â --- each state to be followed with comma and the next state
correct query that will fetch  only state names and not the numbers.
Hi All I am working on a query to get all the datetime values in a column in a table into a comma separated text. eg. ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000 I wanted to get something like 2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.
Begin Quote Calculation<br /> <br />....<br /> Agent Id: 001<br /> Trip Duration: 5days<br /> Relationship Type: Individual<br />....nDestination: AreaTwo<br /> <br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br /> Resolved Relationship: Individual....... /> *Base Rates*<br /> Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br /> Resolved Relationship: Individual....... /> *Base Rates*<br /> Base Rate: 9.070000<br />.....
Result
Trip Duration: 5 days Destination: AreaTwo Base Rate: 6.070000
I am putting together an invoice for my company. I have a text box describing each section of the invoice, followed by a table to list out the charges. I am using multiple tables based on what type of charge the client is receiving.Â
I would like to hide each section if there are no items purchased of that type. I can do this with the table using the expression "=CountRows() < 1", but I do not know how to refer to that table (call it Tablix1 for the sake of discussion) for the text box. I've tried using a ReportItems function as my basis, without success.Â
Hello everyone ! I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image). I would like to know, how you can extract pieces of text from the documents. Example: I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed. I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page. Does anyone has any idea how to work around it ? Best regards. Yannick
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData 1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.] 2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.] 3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set) ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8 1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt 2 [abc1.Gray...abc8.] Gray Shoes Striped jacket 3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like: DECLARE @string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]', @searchString1 varchar(20) = 'abc1', @searchString2 varchar(20) = 'abc2';
SELECT newstring FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int. How can i do this task? Thanks in advance.
I need to normalise comma separated strings of tags (SQL Server 2008 R2).
E.g. (1, 'abc, DEF, xyzrpt') should become (1, 'abc') (1, 'DEF') (1, 'xyzrpt')
I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.
Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').
If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'. Eg: http://www.simpletech.com/upgrades/aopen/s661fxm/s661fxmintelp4/
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number. I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server. Please..advice me. Thanks in advance.
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
Hi All,we have a problem with the Full Text Catalog Search.We use the following SQL Statement for matching companies from a table:select company, lastname, firstname, pkcustomers, fkcustomers,location, title, fkFunktionen, TypeOfPositionfrom customerswhere (fkcustomers = 0 or fkcustomers is null)and active = 1and pkCustomers in (select [KEY] from CONTAINSTABLE(Customers, Company,'"*SEARCHTERMS*"'))order by company ascThe search so far is working perfect.Now the problem: There are two companies in the table called "i-fabrik"and "b-wise". There´s no way to find these two companies. I find outthat the search is successful if there are more than three letters infront of the hyphen (for exampe iii-fabrik or bbb-wise). How can thatbe? Why exactly 3 letters?I hope somebody can help me.Best regardsMarkus Weber
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's. In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this. How can I do this?
Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike
Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.
I have some data that is given to me that has two columns (below is for an example): Column A is an identifiying number, i.e. for a project Column B is a comma separated list of account strings for the project
A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):
What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so: AA.ProjectBuildTower ----- 2222 AA.ProjectBuildTower ----- 3333 AA.ProjectBuildTower ----- 4444 AA.ProjectBuildTower ----- 5555
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.
I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string.
{ tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}} {colortbl ; ed0green0lue0;} {*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par }
It's RichText.
The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off.
The '' seems like it would be the point to start trimming the end off.
I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution.
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
I am trying to load a SQL 2005 table which contains an XML data type using SSIS. The source is a text data file which is driven by a record-type (ie. 01 = course; 02 = conference) that will be used to determine the format of the XML. Being not too familiar with XML, I am struggling with the proper way to generate the XML which I can output to a string column.
Do I use xmlTextWriter for this or someother method?
Is there a way to output the formatted Xml as a string column from my Script Transformation? Should I be using a stream data column?
Are there any good examples that I can refer to? As you can probably tell I am clueless how to handle the XML. Can I even move the xml result to a string???
I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
