How to remove unwanted characters in SQL?

Hi all,

I have a stored procedure that generates the following SQL WHERE clause

UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND

It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.

I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .

Thanks

a quick and dirty way: add

1=1

after the query (which always is good

|||

In your stored procedure, you can use a Substring to remove the end AND:

Assume @.strSQL is the SQL statement string you are returning now. Add this line:

SET @.strSQL=SUBSTRING(@.strSQL, 0,LEN(@.strSQL)-3)

|||

Thanks Limno, that works great

How to remove unwanted characters during ETL?

Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

Inside of an SSIS package, yes. DTS is old news now! You can use the REPLACE() function along with a CHAR() function in a derived column transformation.

To remove tabs: REPLACE([column],char(9),"")|||

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

|||

Andrei Kuzmenkov wrote:

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

CHAR() is too supported, it's just not in the drop down list. You can use it inside the replace function, just as I noted. You just have to type it in there. You could always write 31|||

Andrei Kuzmenkov wrote:

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Yes, you can create Script Transform and easily call custom function from there. Or if it is one-time code, you may simply place it in the Script Transform code as well.

Note that the custom assembly needs to be placed to GAC and because of to limitation of VSA we use for scripting it also should be in Windows\Microsoft.NET\Framework\v2.0.50727 (this is only needed for design time).

|||

In VS2005 I'm getting "The Function CHAR was not recognized".

How to remove non-Numeric or non-alphameric characters from a string in Sql Server 2005

Hi to all,

I am having a string like (234) 522-4342.

i have to remove the non numeric characters from the above string.

Please help me in this regards.

Thanks in advance.

M.ArulMani

Hi,

How about this:

Public Function StripAlpha(ByVal PassedStr)As String' ' Leaves only any numeric values in passed string 'Dim iAs Integer Dim GotCharAs Boolean Dim HoldStrAs String =""Dim HoldCharAs String =""'Clears Leading / trailing spaces PassedStr = Trim(PassedStr)' Cycle through the string to remove unwanted charactersFor i = 1To Len(PassedStr) HoldChar = Mid$(PassedStr, i, 1)If Not IsNumeric(HoldChar)Then GotChar =True Else GotChar =False End If If Not GotCharThen HoldStr = HoldStr & HoldCharEnd If Next i StripAlpha = HoldStrEnd Function

You just pass in something like StripAlpha("xyz()12345") and it should return just the numbers.

Hope this helps.

Paul

|||

Duplicate post. Please use the other thread if anyone would like to contribute:http://forums.asp.net/t/1111146.aspx

How to remove non-Numeric or non-alphameric characters from a string in Sql Server 2005

Hi to all,

I am having a string like (234) 522-4342.

i have to remove the non numeric characters from the above string.

Please help me in this regards.

Thanks in advance.

M.ArulMani

Hi,

How about this:

Public Function StripAlpha(ByVal PassedStr)As String' ' Leaves only any numeric values in passed string 'Dim iAs Integer Dim GotCharAs Boolean Dim HoldStrAs String =""Dim HoldCharAs String =""'Clears Leading / trailing spaces PassedStr = Trim(PassedStr)' Cycle through the string to remove unwanted charactersFor i = 1To Len(PassedStr) HoldChar = Mid$(PassedStr, i, 1)If Not IsNumeric(HoldChar)Then GotChar =True Else GotChar =False End If If Not GotCharThen HoldStr = HoldStr & HoldCharEnd If Next i StripAlpha = HoldStrEnd Function

You just pass in something like StripAlpha("xyz()12345") and it should return just the numbers.

Hope this helps.

Paul

|||

Well I dont recommend looping through the string its better if you use Regular Expressions.

I have bloged about this here :http://dotnetolympians.wordpress.com/2007/01/25/trimming-a-string/

Regular expressions are faster and use lesser code all this above can be done in 1-2 lines of code :).

How to remove new line characters?

I want to display the example record below in a text box without the hard
returns. How can I strip them out so the record displays in one nice line?
The hard return shows as a sqaure box in the sql table, however it does not
paste into this web form. Imagine a sqaure box at the end of each line
below...
Bilat heel and ankle pain when walking and running
Medial knee pain bilat
Anterior compartment strain bilat
Bialt hips hurt w/ occassional low back painreplace(yourTextLine, char(13), ' ')
"Chris Patten" wrote:
> I want to display the example record below in a text box without the hard
> returns. How can I strip them out so the record displays in one nice line?
> The hard return shows as a sqaure box in the sql table, however it does not
> paste into this web form. Imagine a sqaure box at the end of each line
> below...
> Bilat heel and ankle pain when walking and running
> Medial knee pain bilat
> Anterior compartment strain bilat
> Bialt hips hurt w/ occassional low back pain

How to remove {CR}{LF} and Tab {t} characters from source data?

I have built a SSIS package that reads in data from a SQL Server 2005 source database into a flat file destination. The Row Delimiter is {CR}{LF}. The Column Delimiter is Tab {t}.

The data being read from the SQL Server database contains both {CR}{LF} and Tab {t} characters in various fields on several rows.

How can I process the input data from the SQL Server to remove these characters before passing it to the destination output file?

Sorry if this is obvious to all, but I am only just starting with SSIS...

Many thanks

Adrian

If you have the data in a DT_STR ro DT_WSTR column, you can use the Derived Column transform with a REPLACE() function to replace those characters with empty strings.

Otherwise, you might want to use a Script component.

Thanks
Mark

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in messag
e
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>
|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> > I have edited a single company name in the Northwoods Customers
> > table so the CompanyName value begins with a lower case character.
> >
> > When using Query Analyzer to run against Northwoods the following
> > query returns 4 records: the one I edited with the lower case a and 3
> > others where the company's name begins with upper case A
> >
> > SELECT * FROM Customers
> > WHERE CompanyName LIKE 'a%'
> >
> > Thus, case insensitivity appears to be the T-SQL default. How would
> > I get only those records where CompanyName begins with lower case
> > characters?
> >
> > BTW: I'm building one of those alphanumerice linked character menus
> > that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> > have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> > *all* company names whether they begin with an upper case letter
> > or a lower case letter.
> >
> > Using [Aa]% functioned as described above but then I realized I misled
> > myself as I was not able to get *only* records with a company name that
> > began with the lower case character when using [a]% or a%.
> >
> >
> > --
> > <%= Clinton Gallagher
> > A/E/C Consulting, Web Design, e-Commerce Software Development
> > Wauwatosa, Milwaukee County, Wisconsin USA
> > NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> > URL http://www.metromilwaukee.com/clintongallagher/
> >
> >
>

How to query out these annoying New line characters

Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
there were fields with a SQUARE character appended to the end of the values.
I am guessing these are NEW LINE CHARACTERs.
My question is how can I query these characters out because I want to delete
them. I do not want to go one row at a time and erase it!
Thanx in advance
Jiro Hidaka
Programmer for Medisca
PharmaceutiqueA square is unlikely to be a new line character.
Can you figure out what the ascii value is?
SELECT ASCII(SUBSTRING(col, position_where_square_is, 1));
--UPDATE tbl SET col = REPLACE(col, CHAR(result_from_above));
"Jiro" <medisca@.newsgroups.nospam> wrote in message
news:F558657A-D1DB-4083-9C0B-11F01244F366@.microsoft.com...
> Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed
> there were fields with a SQUARE character appended to the end of the
> values.
> I am guessing these are NEW LINE CHARACTERs.
> My question is how can I query these characters out because I want to
> delete
> them. I do not want to go one row at a time and erase it!
> Thanx in advance
> --
> Jiro Hidaka
> Programmer for Medisca
> Pharmaceutique|||What are you looking for - carriage return characters, line feed characters
or both?
char(10) = line feed
char(13) = carriage return
declare @.str varchar(256)
set @.str ='this is
a new line'
select replace(@.str, char(10) + char(13), '')
ML
http://milambda.blogspot.com/|||> select replace(@.str, char(10) + char(13), '')
Actually, most times that pair will be inserted in the opposite order, 13
first, then 10.|||Yes, of course! I was a bit fast with copy/paste.
Thanks for pointing it out!
ML
http://milambda.blogspot.com/