I have a column of 5 comma-separated-value strings:
stringA, stringB, stringC, stringD, stringE
The strings are GUID's with the hyphen stripped and made all uppercase so they are completely random. I need to be able to remove any one of the strings including the comma, in a stored procedure and I am not sure how to accomplish this.
SELECT tickets
FROM users
WHERE CONTAINS (tickets, @ticket)
IF @@rowcount > 0
REMOVE STUFF HERE
SET @valid = 1
ELSE
SET @valid = 0
So if stringB gets passed in as @ticket then the new value in the column would be :
I have a phone number string (416) 555-5555 in a table. I'd like to perform a search on the string so that the user is able to pass any number, and the query returns all phone numbers like it. What I'd like to do is to strip out the brackets and dashes and perform a like search.
I am trying to do string scrubbing in a sql clr function, including removing certain HTML formatting. I would like to use HtmlDecode method, but it's my understanding that System.Web is not available for Sql Clr (without marking code unsafe - not an option for me as this is for an application we sell externally, and unsafe calls woudl not go over well with customers). Is there any class that IS supported for Sql Clr that exposes this functionality? Thanks.
I am looking for the fastest way to strip non-numeric characters from a string.
I have a user database that has a column (USER_TELNO) in which the user can drop a telephone number (for example '+31 (0)12-123 456'). An extra computed column (FORMATTED_TELNO) should contain the formatted telephone number (31012123456 in the example)
Note: the column FORMATTED_TELNO must be indexed, so the UDF in the computed column has WITH SCHEMABINDING.... I think this implicates that a CLR call won't work....
Hi, I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
This algorithm can be used to strip out HTML tags too. With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF ( @rtf VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE@Stage TABLE ( Chr CHAR(1), Pos INT )
INSERT@Stage ( Chr, Pos ) SELECTSUBSTRING(@rtf, Number, 1), Number FROMmaster..spt_values WHEREType = 'p' AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
WHILE 1 = 1 BEGIN SELECT TOP 1@Pos1 = s1.Pos, @Pos2 = s2.Pos FROM@Stage AS s1 INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos WHEREs1.Chr = '{' AND s2.Chr = '}' ORDER BYs2.Pos - s1.Pos
Hi, I use RS 2005 SP2. I add a text item value " smth..." No problem in preview, but export to pdf strip out whitespace characters and then my report in pdf like: "smth..."
hey, what the best way of stripping out a list of characters from a specified field in a table. e.g If first name consists of ABCD'E-FSA, we wnat to strip the ' and the -. There is about 15-20 characters like that. what's the best way of doing it other encapsulating in the replace function that many times. thanks zoey
I have a table that I've imported into SQL - there is a field in there for date that must have used now(); as its default value (access).
So the values are something like:
01/11/2004 12:16:42
I need a way to change the data so that the time element removed is the field just holds the date. Failing that a way to insert this from the existing field into a new field stripping the date off en route would be a great help.
Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!
I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...
so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.
I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.
the ssis expression language getdate() function returns the current date with the current time. i only need to get the current date, without the current time. for example: 9/1/2006
how would i construct the proper expression to return this value?
Is there a way to strip off the time portion of a datetime datatype without changing the datatype? I know I can convert it using CONVERT (NVARCHAR(10), dbo.tblPayments.PaymentDate, 101) but I need to keep it as a datetime datatype?
Hey everyone! I'm doing an export from SQL into excel spreadsheet and then am going to clean out certain parts of the data with global search/replace. The problem is that the SQL data is full of special characters such as |'s and the little box looking characters. How do I export without these characters? I know its possible, I did it about 2 years ago and remember I did some crazy file conversion (make wk3 or something) but I no longer remember Any help would be much appreciated! Thanks, Geoff
PS, attached is a screenshot of the data to give you an idea of what I'd like to strip!
I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..
I'm reading over this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884062&SiteID=1
and I'm kinda lost as to what to do to strip out the dtd from an XML file I am downloading. I do NOT know XSLT and for that reason, I can't follow his logic.
My SSIS package downloads my XML file just fine, now I need to do a strip of the DTD line in my XML Task.
The person who provided the solution in the above post said to do this...
Code Snippet Operation Type: XSLT Source Type: Variable Source: Variable's name containing the xml text Save Operation Result: True DestinationType: Variable OverwriteDestination: True Destination: Variable's name which is to contain the original xml minus the DTD. SecondOperandType: Variable
That stuff I understood. I'll replace variables with my files because they are stored that way, but from what I can tell, that's not my problem.
The stuff he says below this comment is going over my head like a ton of bricks. I can't figure out how to do it.
This is the kind of line of my XML that I want to strip out.
https://www.myaddy.com/pbdr.dtd"[]>
and then he said this...
Code Snippet Since XSL doesn't know about DTDs, telling it to copy everything strips out DTDs. Then use the Variable specified in the Xml task's SecondOperand as the Source data for the xml source.
A note on how to paste a multi-line xml document into a Integration Services String variable:
Integration Services String variables textboxes are not multi-line, in the Windows sense of a line (CR+LF),
So, in order to paste multi-line text (which xml docs almost always are), save a temporary copy with a unix line ending.
That is, create an xml file in visual studio, and paste your sample original xml in there. Go to File/Advanced Save options, and save the xml with the the settings of Encoding: Unicode (utf-8 without signature) - CodePage 65001, and most importantly, set the Line endings dropdown to "Unix (LF)".
After selecting "OK", copy and paste the text from Visual Studio's xml file editor into the IS variable, and you'll note all the xml data appears.
So can anyone walk me through a dummies version of what he is suggesting to do?
I having a column formatted as char(12) being used for indexing. I am not getting the correct max value back from the statement: "SELECT MAX(ID) FROM STAGING_TABLE"
i.e. ID column contains 45,999,1726..The returned val for the query above is 999. Any ideas? TIA
Is there any option to have interlaced strip lines in SSRS Sparklines as shown in the image? The image shown in created in SSRS 2005 using Dundas Charts. I would like to create this microchart in SSRS 2012.
I have a third party app that passes parameters (main dataset query) from the app to SSRS. Unfortunately, when the parameters are passed from the app they will contain equal signs ("=") in front of each parameter. For example, the parameters that may be passed to the main dataset query should be:
"HYDRO, OKO, ONEPL, TECHNI"
However, the parameters that are passed from the app, get to SSRS as:
"=HYDRO, =OKO, =ONEPL, =TECHNI"
Again, this is for the main dataset query and there may be one parameter or there may be any number of them.
Basically, I need to strip the "=" signs from the parameters. whether there is one parameter or ten.
I believe that I will need a custom function to strip the equal signs?
I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--************************************** -- -- Name: A relational technique to strip -- the HTML tags out of a string -- Description:A relational technique to -- strip the HTML tags out of a string. Th -- is solution demonstrates how to use simp -- le tables & search functions effectively -- in SQL Server to solve procedural / ite -- rative problems.
-- This table contains the tags to be re -- placed. The % in <head%> -- will take care of any extra informati -- on in the tag that you needn't worry -- about as a whole. In any case, this t -- able contains all the tags that needs -- to be search & replaced. CREATE TABLE #html ( tag varchar(30) ) INSERT #html VALUES ( '<html>' ) INSERT #html VALUES ( '<head%>' ) INSERT #html VALUES ( '<title%>' ) INSERT #html VALUES ( '<link%>' ) INSERT #html VALUES ( '</title>' ) INSERT #html VALUES ( '</head>' ) INSERT #html VALUES ( '<body%>' ) INSERT #html VALUES ( '</html>' ) go -- A simple table with the HTML strings CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) ) INSERT #t VALUES ( '<HTML><HEAD><TITLE>Some Name</TITLE> <LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD> <BODY BGCOLOR="FFFFFF" VLINK="#444444"> SOME HTML text after the body</HTML>' ) INSERT #t VALUES ( '<HTML><HEAD><TITLE>Another Name</TITLE> <LINK REL="stylesheet" HREF="/style.css"></HEAD> <BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>' ) go -- This is the code to strip the tags out. -- It finds the starting location of eac -- h tag in the HTML string , -- finds the length of the tag with the -- extra properties if any. This is -- done by locating the end of the tag n -- amely '>'. The same is done -- in a loop till all tags are replaced.
BEGIN TRAN WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 ) UPDATE #t SET string = stuff( string , patindex('%' + tag + '%' , string ) , charindex( '>' , string , patindex('%' + tag + '%' , string ) ) - patindex('%' + tag + '%' , string ) + 1 , '' ) FROM #t JOIN #html ON patindex('%' + tag + '%' , string ) > 0 SELECT * FROM #t rollback
hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
