Remote connection failed: return

Showing posts with label return. Show all posts

Friday, March 30, 2012

Remove hex from database

I have someone who altered all of the data in our database [22 million lines
]
that added in a line feed and carriage return in one of the fields. It needs
to
be removed. How do I remove hex values OD OA from a database field?Try something similar to:
declare @.crlf varchar(2)
set @.crlf = char(13) + char(10)
update mytable
set myfield = replace(myfield, @.crlf, '')
TEST ON A DEVELOPMENT DATABASE FIRST !!!!!!
Mike
"jeff fisher" <jeff@.fisher.com> wrote in message
news:OvUhzMCTFHA.2096@.TK2MSFTNGP14.phx.gbl...
> I have someone who altered all of the data in our database [22 million
lines]
> that added in a line feed and carriage return in one of the fields. It
needs to
> be removed. How do I remove hex values OD OA from a database field?

Remove Duplicate Rows

I've got the following table data:

116525.99
116520.14
129965.03
129960.12
129967.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!dale...@.gmail.com wrote:
> I've got the following table data:
> 116525.99
> 116520.14
> 129965.03
> 129960.12
> 129967.00
> And I need to write a query to return only rows 2 and 4, since the
> remaining rows have duplicate IDs. I've tried the Group By, but am
> having no luck.
> Thanks!

What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.

If these are the only two columns you have then probably the best you
can do is:

SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;

or:

SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<dale.zjc@.gmail.com> wrote in message
news:1144699376.547275.246250@.t31g2000cwb.googlegr oups.com...
I've got the following table data:

11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!|||Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

David Portas wrote:
> dale...@.gmail.com wrote:
> > I've got the following table data:
> > 116525.99
> > 116520.14
> > 129965.03
> > 129960.12
> > 129967.00
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> > Thanks!
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
> If these are the only two columns you have then probably the best you
> can do is:
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> or:
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||dale...@.gmail.com wrote:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID
> But I'm getting the following error:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Object.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Data.Time' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Job.KSName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(dale.zjc@.gmail.com) writes:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID

This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.

SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID

For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 28, 2012

Remove CRLF

I have a column in a data flow task which contains carriage return/line breaks. Is it possible to remove them with a derived column - or is there a better way to do this?

thanks in advance

Yeah, it should be possible. Use the REPLACE function. "\r\n" indicates a CRLF

-Jamie

remove chr(10) char with Tsql?

Hello,
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Remove Carriage Return

I attempt to replace the Carriage Return with Space. In
this way, in the SQL Statement, I add this script:
replace(t1.address, char(13), char(32))
However, it seems that it only remove the first carriage
return but not all. Is there any suggestion ?
Thankstry removing the line feed character as well - char(10)
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Peter" wrote:
> I attempt to replace the Carriage Return with Space. In
> this way, in the SQL Statement, I add this script:
> replace(t1.address, char(13), char(32))
> However, it seems that it only remove the first carriage
> return but not all. Is there any suggestion ?
> Thanks
>|||Does it mean that I have to run like this
replace(replace(t1.address, char(13), char(32)), char(10),
char(32)) ?
Thanks
>--Original Message--
>try removing the line feed character as well - char(10)
>
>regards,
>Mark Baekdal
>http://www.dbghost.com
>http://www.innovartis.co.uk
>+44 (0)208 241 1762
>Database change management for SQL Server
>
>"Peter" wrote:
>> I attempt to replace the Carriage Return with Space.
In
>> this way, in the SQL Statement, I add this script:
>> replace(t1.address, char(13), char(32))
>> However, it seems that it only remove the first
carriage
>> return but not all. Is there any suggestion ?
>> Thanks
>.
>|||This is a multi-part message in MIME format.
--060207020402090904070407
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Have you tried
replace(t1.address, char(13)+char(10), char(32))
as Mark suggested? It works for me (see example below).
declare @.tmp varchar(100)
set @.tmp = 'line1
line2
line3
line4
line5'
select @.tmp
select replace(@.tmp, char(13)+char(10), char(32))
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Peter wrote:
>Does it mean that I have to run like this
>replace(replace(t1.address, char(13), char(32)), char(10),
>char(32)) ?
>Thanks
>
>>--Original Message--
>>try removing the line feed character as well - char(10)
>>
>>regards,
>>Mark Baekdal
>>http://www.dbghost.com
>>http://www.innovartis.co.uk
>>+44 (0)208 241 1762
>>Database change management for SQL Server
>>
>>"Peter" wrote:
>>
>>I attempt to replace the Carriage Return with Space.
>>
>In
>
>>this way, in the SQL Statement, I add this script:
>>replace(t1.address, char(13), char(32))
>>However, it seems that it only remove the first
>>
>carriage
>
>>return but not all. Is there any suggestion ?
>>Thanks
>>
>>.
>>
--060207020402090904070407
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Have you tried 
 
replace(t1.address, char(13)+char(10), char(32)) 
 
as Mark suggested? It works for me (see example below). 
 
declare @.tmp varchar(100) 
set @.tmp = 'line1 
line2 
line3 
line4 
line5' 
 
select @.tmp 
select replace(@.tmp, char(13)+char(10), char(32)) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Peter wrote:
<blockquote cite="mid0ea101c518a6$4f165ab0$a401280a@.phx.gbl" type="cite">
<pre wrap="">Does it mean that I have to run like this
replace(replace(t1.address, char(13), char(32)), char(10),
char(32)) ?
Thanks
</pre>
<blockquote type="cite">
<pre wrap="">--Original Message--
try removing the line feed character as well - char(10)
regards,
Mark Baekdal
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.dbghost.com</a>">http://www.dbghost.com">http://www.dbghost.com</a>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.innovartis.co.uk</a>">http://www.innovartis.co.uk">http://www.innovartis.co.uk</a>
+44 (0)208 241 1762
Database change management for SQL Server
"Peter" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I attempt to replace the Carriage Return with Space.
</pre>
</blockquote>
</blockquote>
<pre wrap="">carriage
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">return but not all. Is there any suggestion ?
Thanks
</pre>
</blockquote>
<pre wrap="">.
</pre>
</blockquote>
</blockquote>
</body>
</html>
--060207020402090904070407--|||Thank you for your advice.
When I use char(13)+char(10), most of the problems are
fixed.
However, for unknown reason, there is record that works
fine with char(13) only becomes like this:
Aboukir Street
MONGKOK ABN 3355
Do you have any suggestion ?
Besides, when I use Notepad, is it possible for me to see
whether it is only CR or CRLF ?
Thanks
>--Original Message--
>Have you tried
> replace(t1.address, char(13)+char(10), char(32))
>as Mark suggested? It works for me (see example below).
>declare @.tmp varchar(100)
>set @.tmp = 'line1
>line2
>line3
>line4
>line5'
>select @.tmp
>select replace(@.tmp, char(13)+char(10), char(32))
>--
>*mike hodgson* |/ database administrator/ | mallesons
stephen jaques
>*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61
(408) 675 907
>*E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
>Peter wrote:
>>Does it mean that I have to run like this
>>replace(replace(t1.address, char(13), char(32)), char
(10),
>>char(32)) ?
>>Thanks
>>
>>--Original Message--
>>try removing the line feed character as well - char(10)
>>
>>regards,
>>Mark Baekdal
>>http://www.dbghost.com
>>http://www.innovartis.co.uk
>>+44 (0)208 241 1762
>>Database change management for SQL Server
>>
>>"Peter" wrote:
>>
>>I attempt to replace the Carriage Return with Space.
>>
>>In
>>
>>this way, in the SQL Statement, I add this script:
>>replace(t1.address, char(13), char(32))
>>However, it seems that it only remove the first
>>
>>carriage
>>
>>return but not all. Is there any suggestion ?
>>Thanks
>>
>>.
>>
>sql

Monday, March 26, 2012

Remove alpha chars & all spaces in field [UPDATED]

Hello,

I need to strip out all alpha chars and spaces in a given field and return only the numbers.

I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).

Any help would be appreciated.

UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?

Hi,

If it is possible to call the SQLServer function Please write the following function:

create FUNCTION [dbo].[GetNumberFromData]

(@.data VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @.pos BIGINT,@.char CHAR(1),@.rightside VARCHAR(MAX)

/*Set @.data = rtrim(ltrim(@.data))

While CharIndex(' ',@.data)>0

Select @.data = Replace(@.data, ' ', '')*/

SET @.pos = 1

WHILE (@.data like '%[^0-9]%') AND @.pos <= LEN(@.data)

BEGIN

SET @.char = SUBSTRING(@.data, @.pos, 1)

IF @.char like '%[0-9]%'SET @.pos = @.pos + 1

ELSE

BEGIN

SELECT @.rightside = SUBSTRING(@.data, @.pos + 1, LEN(@.data)),@.data = CASE WHEN @.rightside like '%[0-9]%' THEN

LEFT(@.data, @.pos - 1) + @.rightside

ELSE

LEFT(@.data, @.pos - 1)END

END

IF @.data like '%[^0-9]%' OR LEN(@.data) = 0

SET @.data = NULL

RETURN LTRIM(RTRIM(@.data))

End

and call the function like this:

If you can't able to call the function from report put the below statement in the stored procedure.and call that SP in your report.

select [dbo].[GetNumberFromData](' 1 800 , dd 798 hjjhjj kal 8976 asdf ')

Hope this helps.

Thanks

|||

Hello,

Here's a custom function I wrote that will strip all the non-numeric characters from a string. Go to Report Properties from the report menu, then in the Code tab, enter this:

Public Function GetNumbers(s as string) as Integer

Dim str as String

Dim i as Integer

str = ""

i = 0

While i < Len(s)

If (Asc(Mid(s, i + 1, 1)) >= 48 and Asc(Mid(s, i + 1, 1)) <= 57)

str = str + Mid(s, i + 1, 1)

End If

i = i + 1

End While

return cInt(str)

End Function

Then, in your textbox for the numbers, use this as your expression:

=Code.GetNumbers(Fields@.!Info.Value)

As for your Update... yes, you can use them both at the same time. =Right(Replace(Fields!Info.Value, " ", ""), 10)

Hope this helps.

Jarret

|||Thanks a BUNCH!

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.
You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))) ,0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> > Hello.
> >
> > How can I write some stored procedure or function to which I will pass =the
> > string and it will return me only numbers from my string?
> > Exactly I need to remove all non digit characters from some string.
> >
> > Tank you.
> >|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0- 9]%',SUBSTRING(@.InputString,@.i+1,len(@.In
putString))
),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

remove .000 from the end of the decimal number

Hi experts,
I have a variable with type decimal(9, 3). Is it possible to return the
values without 0 at the end when it is unecessary ?
Examples:
1.000 should return 1
0.750 should return 0.75
0.500 should return 0.5
0.125 should return 0.125
Or should I write the function for that conversion?
Thank you!
Ramunas BalukonisDECLARe @.x AS decimal(9, 3)
SET @.x=1.000
SELECT CAST(@.x as numeric(9,0))
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1118133303.631018@.loger.vpmarket.int...
> Hi experts,
> I have a variable with type decimal(9, 3). Is it possible to return the
> values without 0 at the end when it is unecessary ?
> Examples:
> 1.000 should return 1
> 0.750 should return 0.75
> 0.500 should return 0.5
> 0.125 should return 0.125
> Or should I write the function for that conversion?
> Thank you!
> Ramunas Balukonis
>|||Roji
It won't work for 0.750 as the OP asked
Just for fun
declare @.w as decimal(9,3)
set @.w=0.125
select case when point=0 then cast(@.w as varchar(10))else
left(@.w,point-1)end
from
(
select charindex('0',cast(@.w as varchar(10)),
charindex('.',cast(@.w as varchar(10)),1)) as point
) as der
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:e9hoy4zaFHA.2884@.tk2msftngp13.phx.gbl...
> DECLARe @.x AS decimal(9, 3)
> SET @.x=1.000
> SELECT CAST(@.x as numeric(9,0))
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1118133303.631018@.loger.vpmarket.int...
>|||Uri,
try with 0.105
declare @.w as decimal(9,3)
set @.w=0.105
select case when point=0 then cast(@.w as varchar(10))else
left(@.w,point-1)end
from
(
select charindex('0',cast(@.w as varchar(10)),
charindex('.',cast(@.w as varchar(10)),1)) as point
) as der
returns 0.1
Ramunas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23DJECG0aFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Roji
> It won't work for 0.750 as the OP asked
> Just for fun
> declare @.w as decimal(9,3)
> set @.w=0.125
> select case when point=0 then cast(@.w as varchar(10))else
> left(@.w,point-1)end
> from
> (
> select charindex('0',cast(@.w as varchar(10)),
> charindex('.',cast(@.w as varchar(10)),1)) as point
> ) as der
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:e9hoy4zaFHA.2884@.tk2msftngp13.phx.gbl...
the
>|||Not very elegant but I believe this works:
DECLARE @.TestVar AS DECIMAL(9,3)
SET @.TestVar = 1.750
SELECT REPLACE( REPLACE( REPLACE( REPLACE(
CAST(@.TestVar AS VARCHAR(10)) + '#'
, '0#', '#'), '0#', '#'), '.0#', '#'), '#', '')
Need to add an extra inner replace for each additional decimal place
Yours
Alasdair Russell
"Ramunas Balukonis" wrote:

> Hi experts,
> I have a variable with type decimal(9, 3). Is it possible to return the
> values without 0 at the end when it is unecessary ?
> Examples:
> 1.000 should return 1
> 0.750 should return 0.75
> 0.500 should return 0.5
> 0.125 should return 0.125
> Or should I write the function for that conversion?
> Thank you!
> Ramunas Balukonis
>
>|||Ramunas,
This will just about do it:
replace(rtrim(replace(@.d,'0',' ')),' ','0')
It will return 1. instead of 1 with input 1.000, and it that is important,
use this:
replace(rtrim(replace(replace(rtrim(repl
ace(@.d,'0',' ')),' ','0'),'.','
')),' ','.')
Steve Kass
Drew University
Ramunas Balukonis wrote:

>Hi experts,
>I have a variable with type decimal(9, 3). Is it possible to return the
>values without 0 at the end when it is unecessary ?
>Examples:
>1.000 should return 1
>0.750 should return 0.75
>0.500 should return 0.5
>0.125 should return 0.125
>Or should I write the function for that conversion?
>Thank you!
>Ramunas Balukonis
>
>

Monday, March 12, 2012

Remote Query

Hi,
I would like to know why SQL Profiler return the output shown below and is
it possible to tune and avoid this problem?
declare @.P1 int
set @.P1=7
exec sp_prepexec @.P1 output, N'@.P1 varchar(255)', N'SELECT Tbl1019."Name"
Col1075,Tbl1019."PhoneNumber" Col1080,Tbl1019."Pager"
Col1081,Tbl1019."FirstName" Col1076,Tbl1019."LastName" Col1077 FROM "Users"
Tbl1019 WHERE @.P1=Tbl1019."Email"', 'aaa1@.test.com'
select @.P1
exec sp_execute 7, 'aaa2@.test.com'
exec sp_execute 7, 'aaa3@.test.com'
...
exec sp_execute 7, 'aaa10@.test.com'
If I have a page which need to display 10 users then it will show 10 times.
Thanks,
KennyThats the way your provider executes it at the SQLServer side, there is
AFAIK no way to change it, unless you code your Exec string on your own
in your application.
What data provider are you using ?
HTH, Jens Suessmeyer.|||I am not talking about any application but just the T-SQL.
You can simulate this problem by just setup 2 database server. DB1 have
users data. DB2 have Jobs data. You need to have a view vwUser in DB2 which
select the user data from DB1 e.g. FROM DB1.sd_db01.dbo.Users.
After that you try to execute SELECT * FROM vwUser and capture info from
profiler then you can see the problem.
Thanks,
Kenny
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126854669.318346.268580@.g44g2000cwa.googlegroups.com...
> Thats the way your provider executes it at the SQLServer side, there is
> AFAIK no way to change it, unless you code your Exec string on your own
> in your application.
> What data provider are you using ?
> HTH, Jens Suessmeyer.
>

Friday, March 30, 2012

Remove hex from database

Remove Duplicate Rows

Wednesday, March 28, 2012

Remove CRLF

remove chr(10) char with Tsql?

Remove Carriage Return

Remove Carriage Return

Remove Carriage Return

Monday, March 26, 2012

Remove alpha chars & all spaces in field [UPDATED]

Remove all non digit characters from some string

Remove all non digit characters from some string

Remove all non digit characters from some string

remove .000 from the end of the decimal number

Monday, March 12, 2012

Remote Query

Remote connection failed

Blog Archive

About Me