How To Strip A List Of Characters From A Field

Nov 8, 2001

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

View 2 Replies


ADVERTISEMENT

Strip Certain Characters

Jul 13, 2004

Can anyone tell me how I can strip certain chahrcters from a string

I know I can use replace, but i don't think this is appropriate for what I want to do

For example I have the string

declare @text varchar (100)
select @text = 'word1, word2 & word3'

if i do a replace on the string like this
select @text = 'replace(@text, ',', '')
select @text = 'replace(@text, '&', '')

I end up with the string
select @text = 'word1 word2 word3'

i.e. 2 spaces between word2 and word3

What i want the string to look like is :
select @text = 'word1 word2 word3'

Is there a way i can check for more than one space + characters ( / , &) in one go

many thanks

View 4 Replies View Related

Strip Characters Out Of String

Jan 11, 2002

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.

View 4 Replies View Related

Data Export - How To Strip Out The Special Characters

Nov 10, 2005

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!

View 1 Replies View Related

Transact SQL :: Strip Non-numeric Characters From String

Jul 27, 2015

If I have a string with the following values, I’d like to replace the non-numeric characters with a space.

Input
01234-987
012345678
01234 ext 65656
Tel 0123456
012345 898989

Output
01234 987
012345678
01234 65656
0123456
012345 898989

View 20 Replies View Related

SQL Server 2012 :: Strip Non-numeric Characters From A String

Jul 15, 2015

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....

View 9 Replies View Related

Reporting Services :: Strip Unwanted Characters From Parameters For SSRS Report

May 22, 2015

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?

View 5 Replies View Related

Need To Strip Non-numerics From Phone Field

Jul 22, 2004

I tried searching forums for the last hour and could not find much.

Rather then doing...

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(ISNULL(PHONE, ''), ' ', ''), '(', ''), ')', ''), '-', ''), '.', ''), ':', ''), '+', '')

I was hoping there was an equally efficient alternative that utilizes PATINDEX('%[^0-9]%', PHONE)

to some capacity (i.e. uses regular expressions to strip all non-numeric characters from the phone field)

I am certain this has been addressed before, just not sure if the "REPLACE to the nth degree" is the only solution.

THANKS!

View 7 Replies View Related

Strip Time Out Of A Date Field - Sql

Dec 3, 2004

Hi all,

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.

Many thanks

Phil

View 2 Replies View Related

How Can I Store Over 16000 Characters To Sql Table Field With Language Specific Characters?

Feb 19, 2008

In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
 
Thanks
 
 

View 3 Replies View Related

A Relational Technique To Strip The HTML Tags Out Of A Ntext Datatype Field

Nov 27, 2007

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

View 1 Replies View Related

Convert Field From VarChar To Int With Speical Characters In Field

Aug 29, 2007

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

View 2 Replies View Related

Special Characters In A Dropdown List

Nov 2, 2006

I have a web form that needs to be bilingual. It contains several dropdown lists. The "text" and "value" for each item in the dropdown list is retrieved from a database. The logic reads the database and then populates the various dropdowns with logic such as:

dim newItem as new ListItem(textString, valueString)

theDropDown.items.add(newItem)

The problem that I am having occurs when the textString needs to contains special characters such as accented vowels or an "n" with a tilde. I have tried populating the database with HTML-formatted strings such as "Avi&oacute;n", hoping that this would display "Avion" with an accute accent over the "o". Unfortunately it displays "Avi&oacute;n".

How do I get dynamically-created dropdowns to display special characters? If the answer is to simply store the special characters directly in the database, then the question becomes how do I get the special characters into the database? I tried to figure out how to write a T-SQL command such as

update tablex set SpanishName = 'Avion' where id=999

but I can't figure out the syntax to insert an accented 'o' into the string.

Thanks.

View 4 Replies View Related

Urgent-Replacing A List Of Characters From A Column

Nov 13, 2001

Hi,
this is my second attempt to get an answer to this question.
We want to strip our firstName column and lastname column of any punctuation that might be present.
What's the best of doing that?
Is my only choice to write a nested REPLACE for each character we want replaced (which will end up being very very long) or is there another way.
thanks
Zoey

View 2 Replies View Related

Allowing Users To Type In Characters In A Drop Down List?

Feb 6, 2007



is this even possible in reporting services? I already deployed reports for our client using Reporting Services 2000, one of their complaints was that the dropdownlist of the reports contains a very long list of data, this list cannot be shorten since all data are required. Is there a way to let the users type in characters, not only one character to find the exact data they want. The data in the drop down list are needed because these data are parameters.



Are there any web based reporting tools which can provide this kind of requirement?

View 1 Replies View Related

Passing A List Of Numbers To A Stored Procudure, Having A Size More Than 8000 Characters

Aug 1, 2007

Hi..

I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@Items varchar(100) --- List of numbers
)
AS Begin

Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)


This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP??






View 4 Replies View Related

How Many Characters Has My Field?

Feb 3, 2005

Does anyone know a function in SQL or how I can get the amount of characters of a field?

I have a column named NU_IPS wich contains data varchar type, that has a % symbol at the end, like 9.7% and so on... But in original table it can't be like this (it has to bem float type), I just want the number content, like this 9.7 For that I need in DTS put a query that convert it. That's why I need a function or something that can get the quantity of characters of each field.

So, It would be someting like this...

select substring(convert(varchar(getSizeField() - 1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

It would cut always the last caracter, wich is '%'...

Any clues?
All posts are welcome, thanks.

View 5 Replies View Related

Updating 3 Characters In A Field

Mar 5, 2002

I have a sql database that includes a table of customer contact information. The area code for many of my customers is about to change. Is there a way to mass update the phone number field so that all phone numbers that currently start with 111 change to 222 ? Ex 1115554444 to 2225554444 ?

Thanks in advance.

View 2 Replies View Related

Sp To Extract Characters From One Field To Another

Aug 15, 2007

Hello,

I have a table in sql server that has a field with numeric characters, i would like to select some but not all characters from this field and insert it into a new field. The field that i want to get some characters from has other characters that i dont need, It has a lot of zero's next to the numbers that i need. so far i created a stored procedure, but its not working. I want to extract the characters and insert them into a new field in the format "000.00" or "00.00". The values are either hunderths or tenths, which makes it difficult for me to decide which characters to ignore. Please see my SP created so far below:

CREATE PROCEDURE dbo.spUpdatePaymentDetails
AS UPDATE dbo.PaymentDetails
SET NewAmount = SUBSTRING('00' + TransactionAmount, 1, LEN('00' +TransactionAmount) - 2) + '.' + SUBSTRING('00' + TransactionAmount, LEN('00' + TransactionAmount) - 1, 2)
GO

the character "." is not in the original field so therefore i would like to insert it into the new field.

Please help urgently.

Tnx

View 2 Replies View Related

Query/select The First 10 Characters In A Field

Jul 25, 2007

I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B.

So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi'


I know I've done this before but can't find a good reference.

It's something like this when calling a query within a WHILE loop:

SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8)


Am I close? Any help would be appreciated.

View 3 Replies View Related

Unlimited Number Of Characters In A Field

Feb 4, 2005

Hello,

I am developing a message board using ASP on IIS and SQL Server 2000, and I am running into a problem.

When a user enters their post, they enter their user name, subject, and of course all of their post content. Now the post content should be an unlimited number of characters since it could end up being multiple paragraphs.

I know I could just use an ASP file system object and write the text to a file, but I wanted to save all of these paragraphs as a field in a database table so I could pull it from the database so it could be edited by the user.

Is there a way to make a field be able to take in an unlimited amount of characters?

Or is this the wrong way to do things; is it bad to have an unlimited amount of characters in a table field? Should I stick to using the ASP System File Object?

View 1 Replies View Related

Replacing Characters In A Text Field

Jul 20, 2005

I have a large table, tblMessage, which stores e-mail messages in textfields. I need to remove the carriage returns the data in these fields,but I have not yet figured out how to do so.I thought that the way to do this would be with the REPLACE function;unfortunately, of course, the REPLACE function cannot work with TEXTfields. I tried CASTing the text field to VARCHAR(8000); however, someof the rows have more than 8000 characters in the text field, so it bombs.Here is the SQL that I tried:selectmsgID,msgSent,msgFromType,msgFromID,msgSubject,REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') ASnewMessage,msgOriginal,attIDinto tblMessageNewfrom tblMessageI'm at my wit's end. Truncating the text field to 8000 character is anacceptable option, but I can't even seem to be able to do that.I'm using SQL Server version 7.

View 2 Replies View Related

Removing Characters In Field Grouping

Jun 15, 2007

Hello,



I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?






Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

View 2 Replies View Related

How To Confine A Field To Be Number Characters Only?

Jun 1, 2006

i have a field which is CHAR(20), and it is allowed to only containe number chars.

is there any collation_name can help ? or how can i set the check clause ?

View 9 Replies View Related

How To Ignore The Last 20 Characters In A Field While Matching

May 22, 2008



I need to pull address info and need to match customername from a spreadsheet.
Due to bad design, in our database instead of having a 'status' for the customer,
they just added ' - ACCOUNT CANCELLED' to the customername

Customername (database)
Tonkin Wilsonville - ACCOUNT CANCELLED
Subaru of Riverside - ACCOUNT CANCELLED


Customername (spreadsheet)
Tonkin Wilsonville

Subaru of Riverside

How can I match the names in the spreadsheet with the names in the database, I tried to use like, but can't get it to work for multiple rows.

Thanks
Susan

View 3 Replies View Related

VARCHAR Field Limited To 1024 Characters

Jul 28, 2005

Hello all,I have a field defined as VARCHAR(8000) yet it only accepts a maximum of 1024 characters. Does anyone know how I can save 8000 characters in a single field?Thanks,Bill.

View 2 Replies View Related

Ntext Field Limiting Input To 64,999 Characters

May 24, 2002

I am having an interesting error. I have a bunch of data that I am updating to a field in my sql server 7.0 database. It updates the data, however only puts the first 64,999 characters into the field. The datatype is ntext. From what I understand, ntext datatype can hold much more than 65,000 characters. Am I right in this? The data I'm putting into the field is html tags/text. I've tried several different data sets. I'm updating the data using Coldfusion/SQL commands.

Here is my SQL syntax:

UPDATE htmltest
SET html_offline = '#form.html#'
WHERE htmlid = #form.htmlid#

View 1 Replies View Related

Display Over 256 Characters For VARCHAR(3500) Field

Jan 12, 2006

Hopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.

How do I go about extracting ALL of the data from this field? Any help is much appreciated.

Thanks in advance.

View 1 Replies View Related

Transact SQL :: Replacing Invalid Characters In A Field

Oct 6, 2015

When we are getting data in a table and we want to replace characters with other characters. For example, We have a table with a street address, and there are numerous ascii character values we want to review and replace if they exist. We were looking at using a table with 2 columns, 1 containing each ascii character value the other it's preferred replacement value. Then updating the street address searching through each ascii character and replacing it if needed. Currently, we are running it through a looping process searching each individual address for each ascii character, and updating it.

View 4 Replies View Related

Search String Field (nvarchar) For Unicode Characters

Nov 8, 2006

MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail:
SELECT * FROM Expression WHERE ExpText LIKE '%≤%'   -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText)   -- no records
However, SELECT PATINDEX('%≤%', 'y ≤ 200')  will return 3.
Any suggestions? Thanks in advance.

View 3 Replies View Related

Can I Make The Size Of The Field Greater Than 1023 Characters?

Dec 17, 2001

is there any way i can make a field in a table accomodate more than 1023 charcters? i used the 'varchar' datatype and used a length of 2500, but still, I can't fill up a field with more than 1023 characters. Is there any way to change it?
Also, is there any way to used a symbol or special character in a field? Can SQL server identify such a character? like the alpha or beta symbol...

Thank you...

View 1 Replies View Related

Maximum Number Of Characters In A Database Table Field

Feb 27, 2003

Can any one help me, i'm building a dynamic database driven site using dreamweaver and MS SQL2000 andi'm haveing problem storing over 8000 characters in a table filed (IE: it wont let me!!) is there a special table field value that i need to set to get more characters in a table field or is this a limitation of SQL.

Any help or suggestions would be appreciated

Regards
B

View 3 Replies View Related

Problem Updating Null Text Field To 256 Characters

Aug 9, 1999

If I create a row with a nullable text column whose initial value is null and then update the column with a value that is exactly 256 characters long, the value remains null. Once I update the column to any other value (including null), it works as expected. I have not yet seen a way around this.

I am working in 6.5 SP3.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved