Stripping Punctuation
Oct 24, 2005Has anyone had to deal with removing punctuation (commas, apostrophes,
etc.) from a column? What is the most efficient way to have these
characters automatically removed from the column?
ADVERTISEMENT
Punctuation ?
Mar 12, 2004Hi
I have this simple and working well SQL-row:
MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' ", MyConnection)
-----------
Then I have this one which I am very proud of because I can retrieve any word or part of word from the Item-field:
Test_Text=(Session("session-Text").tostring)
Dim val1, val2, val3 as string
val1 = Test_Text
val2 = "%"
val3= val1 + val2
MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %' ", MyConnection)
------------
The problem is:
When I try to merge these two in one big "AND" row as shown below, It only cares of picking up the Test_Text string and ignores totaly the TestType one!
I guess it is a matter of punctuation, can any one help me? Thanks.
MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' AND Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %'", MyConnection)
Convert Punctuation To Spaces?
Jul 20, 2005Hi,I have a table of text. I need to search for whole words within this text...For example, I need to be able to search for records that contain 'dog' butnot return 'hotdog' or 'dogma' for example.I am doing this by throwing a space around both the records in the table andthe search word like this:WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')The problem is that punctuation needs to be stripped out of the text so thatit will still find "...walking the dog."Is there a way to update, converting a certain set of characters intoanother character (i.e. a space) and/or to do the same thing during the wordsearch query itself?Thanks!
View 5 Replies View RelatedReserved Symbols / Punctuation Marks In Passwords
Dec 9, 2003Please can anyone help me - I am trying to establish whether there are any reserved symbols in SQL passwords
eg % £ " $ ( / < * etc
The SQL BOL has a list of reserved words but I cannot find a list of reserved symbols.
We have a problem where by for example if the main part of the password is "test" and my user wants to add a punctuation mark into this we get "You are attempting to use an invalid character message"
Specifically
"test$" is accepted "$test" is not accepted
None of "te+st" "+test" "test+" are accepted.
Has anyone else come across this and found a list of the logic that produces these results so I can point my users in the right direction?
Cheers
Stripping Off Last Few Characters
Feb 23, 2004I want to strip off the last three characters from an item number. The only thing is tht every item number is not the same lenght. The last three characters of this number are packing codes that I do not need. Fore example I can have all these numbers:
EFJ50701033
EFW1546066
RFM4925156
70561033
89541899
How would I remove the last three characters and only remain with whatever is in front of those three characters?
Any help is greatly appreciated!
Stripping .rtf Tags Out
Sep 25, 2007Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
View 16 Replies View RelatedSql Query Stripping Hyphens
May 23, 2006Hi everyone,
I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this
213-456-9999
and some are
2136789999
Please let me know if this is possible.
Thanks.
Stripping Off Numbers In A List
Apr 28, 2008I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
thanks
Stripping And Resetting Security
May 29, 2008Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?
The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have.
Stripping A Date From A Varchar2
Nov 29, 2006Hi All,
I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:
JSMITH 1/1/2006 1:00:00AM
I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?
Stripping Out Numbers From A String
Oct 25, 2006Hi
I have a field that starts with numbers and then has a description after if, eg.
"123 This is the description for string 123"
1. How would I go about stripping out the first instance of number to leave the string as "This is the description for string 123"
thanks.
Bill
T-SQL (SS2K8) :: Stripping First And Last Name From Email Address
Mar 12, 2015I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
[Code] .....
Stripping Decimal Data From An Nvarchar Value...
Nov 20, 2006I have a table that contains an nvarchar column of data. The data is actually a monetary value; sometimes with a decimal point sometimes without.
My problem is that I need to strip the decimal portion of the string if it exists. From a select statement I can use:
SUBSTRING(DW_OBP_ORD_TMP_IC.VALUE,1,LEN(VALUE)-(LEN(VALUE)-CHARINDEX('.',DW_OBP_ORD_TMP_IC.VALUE)
if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.
I need to pull the data from the table, along with many other fields. How do I do this without using a cursor? I have millions of rows so need a solution that will be quick.
thanks in advance,
Marilyn
Stripping Non-Numeric Characters From A String Function
Jul 9, 2002Hi,
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
SQL Server 2008 :: Stripping Part Of A File Name
Oct 16, 2015I have a filename as follows:
123_20151016_3152_AIRHtest1.txt
What I would like to extract from this fill name is the "3152" only. What is the correct way to do this?
Stripping Input Mask From Phone Numbers
Mar 28, 2006Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
View 2 Replies View RelatedStripping Non-numeric Chars From A Field String - SQL 2000
Jul 20, 2005Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
View 1 Replies View RelatedExport From SQL Money To DT_CY Stripping Off Trailing Zeros
Sep 28, 2007
I am exporting from a SQL money field to a flat file. The datatype is set to DT_CY in SSIS. I need two decimal places in the flat file but instead I am getting 1.1 instead of 1.10.
Should I be using a different datatype or is there a way to set the number of decimals on the DT_CY datatype?