Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Removing Spaces From An Nvarchar Column


Hello,
This is a simple question, hopefully with a simple answer. I have
an nvarchar column of length 255. In one of the rows I have the
following sentance - 'See the brown ball bounce'. Is it possible to
use a command to remove all of the spaces in that sentance, so that
the sentance reads 'Seethebrownballbounce'? As you can see, I am not
just interested in getting rid of the trailing and leading spaces.

Thanks,
Billy


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Removing White Spaces In A Varchar Column
I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad

View Replies !   View Related
Removing Spaces From A Text
Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:

UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')

so, i tried this query and it does nothing.

UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')

does anybody have any ideas? Thanks!

View Replies !   View Related
Removing Words And Spaces Using DTS
I have a table of addresses I need to import each month and under the 'County' Column the word County or Burrough follows each county name. Ex 'Baltimore County', 'Washington County', 'Davis Burrough' etc.

Using DTS is there a way to remove the space and County(Burrough) following each county name? I've tried using the trim feature but that only seems the Trim the White Space.

Thanks for any help you can offer.

View Replies !   View Related
Reportviewer Removing Spaces
Hi there,

I have a text box with the following expression:

="OEM Part Code" + "           "  + "Part Code" +"                           " + "Part Description"

As you can see, there is a lot of spaces in here. The reportviewer removes these spaces. so that it look like
OEM Part Code Part Code Part Description"

Instead of
OEM Part Code           Part Code                           Part Description

Why is it doing this?
Can I stop it from doing this?

Regards
Mikey

View Replies !   View Related
Removing Spaces Between Words In Sql
I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space and returns @data. What I additionally need the function to do is scrunch up @data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').

What I need help in doing is the "Scrunch" part. Is there a way I could move the @Data to something like @DataHold and inspect each character, if it is not a blank, move that character back to @Data?
This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.

Thanks for any help!

 

View Replies !   View Related
Trailing Spaces In Nvarchar Columns
 

Hi All,
 
For  €śnvarchar€? column if we pass the value as spaces, then it stores as space.
 
Eg:
 

declare @path nvarchar(50)

set @path = '       '

select '=>' + @path + '<='


 
Is there any setting / configuration that will force it to automatically trim the spaces and store it as null?
 
Thanks in Advance,
Palani

View Replies !   View Related
UPDATE - Removing Trailing Spaces
I have three columns, RecordID, FirstName, and LastName, but somehowthrough some program glitch, there is sometimes a trailing space inthe firstname and lastname columns, for example, a persons name couldbe entered as "John " "Smith" or "Bob " "Johnson "I know there is a RTRIM function in sql, but the problem I/m having ismaking an update line go through each row, and removing trailingspaces on those two columns. Any help will be greatly appreciated.Thanks in advance.

View Replies !   View Related
Removing Leading And Trainling Spaces
I loaded data into a CHAR fields from Legacy.
How to remove those leading spaces with in SQL server...?

View Replies !   View Related
Removing Records With Spaces In Fields
How do i eliminate records which has spaces

i tried with

select * from table
where col1 !='' or
col1 is not null

which doesn't work.. any help.

View Replies !   View Related
Removing Non-alpha Characters && Spaces Script...
Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)

SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END

View Replies !   View Related
Removing Empty Spaces Between Main Report And Subreport
Hi All,

I have a main report that does some row groupings.. When there is little data coming out, there tends to be a widish gap between main report and sub report..

I tried placing the sub-report in the table footer but this landed up squashing the column width's of the sub report..

Is there another way to get this right ? I tried placing the sub report into a rectangle, and the main table into its own rectangle and then merging those into another rectangle.. This unfortunately did not work..

Any Suggestions would be appreciated

Kind Regards,
Neil Stuppel

View Replies !   View Related
Keeping Trailing Spaces On Function Returning Nvarchar(4000)
I'm trying desparately to write a PadRight function in SQL Server 2005.  I seem to be failing miserably because the trailing spaces disappear when the data is returned.  First of all, why does SQL Server think I want my string trimmed?  And second, how do I overcome this?  Code below:
 



Code Snippet
CREATE FUNCTION [dbo].[PadRight]
(

@sourceString NVARCHAR(4000),
@length INT,
@padCharacter NCHAR(1) = ' ',
@trimBeforePadding BIT = 1
)
RETURNS NVARCHAR(4000) AS
BEGIN

DECLARE @returnStringLength AS INT, @toReturn AS NVARCHAR(4000)
SET @toReturn = LEFT(@sourceString, @length)
 

IF @trimBeforePadding = 1

SET @toReturn = RTRIM(LTRIM(@toReturn))
SET @returnStringLength = LEN(@toReturn)
IF @returnStringLength < @length

SET @toReturn = @toReturn + REPLICATE(@padCharacter, @length - @returnStringLength)
RETURN @toReturn
END
GO
 
 

View Replies !   View Related
Getting Spaces In A Column
I have a single column value like this.  I want to get the charindex value whereever it is a single space.

 
'6 REP5426 15936 022708 107/0000 33003301985 BAILEY AMY NICOL 11454 PP 25.00 000-00 00110080 A O '
 
Thanks.

View Replies !   View Related
All Spaces In A CHAR(5) Column
i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?

View Replies !   View Related
Inserting A Value With Spaces Into A Column
Hello,

I am fairly new to SQL I have started to administer a system which handles carrier information for a mail order system. The logic behind the system is quite simple there are 5 or so columns in a table the first column is the first part of the postcode i.e EX15, the other columns contain which delivery services and depot numbers are associated with that postcode. It works fine at the moment.

However now the main carrier has decided that they are now going to split these postcodes so for example EX15 1* goes to a different depot than EX15 2*

I cant seem to insert EX15 1 into the first column, I get the following error:

Attempt to store duplicate value in unique column. (-155)

Is this because of the space in 'EX15 1'? Because 'EX15' already exists in that column? In which case do I need to somehow tell SQL that there is a space there?

I hope this makes sense

Below is a snap of the table with the existing EX15 postcode data

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15.....|NF/S93......|........1|34........|C.......|DEVO
EX15.....|NF/SAT......|........1|34........|C.......|DEVO
EX15.....|NF/930......|........1|34........|C.......|DEVO
EX15.....|NF/AM.......|........1|34........|C.......|DEVO
EX15.....|NF/ON.......|........1|34........|C.......|DEVO
EX15.....|NF/48.......|........1|34........|C.......|DEVO
EX15.....|NF/3D.......|........1|34........|C.......|DEVO

Here is a snap of how I would like to set up another entry:

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15 1...|NF/S93......|........1|34........|C.......|DEVO
EX15 1...|NF/SAT......|........1|34........|C.......|DEVO
EX15 1...|NF/930......|........1|34........|C.......|DEVO
EX15 1...|NF/AM.......|........1|34........|C.......|DEVO
EX15 1...|NF/ON.......|........1|34........|C.......|DEVO
EX15 1...|NF/48.......|........1|34........|C.......|DEVO
EX15 1...|NF/3D.......|........1|34........|C.......|DEVO

Thanks

View Replies !   View Related
Spaces In Column Names
Hi all,

Is it a bad practice to create column names with spaces. like [Last Modified On]?

If yes, y?

View Replies !   View Related
Need Your Help To Remove Spaces In The Column Entries Using SQL
Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance

View Replies !   View Related
Fill Up Spaces With Dots In A Column
Hi,

I currently have a column in a table with data type char and length 500.  However, not every column fills up the entire 500 length, and I would like to fill up the rest of the spaces with dots.  Is there a setting in SQL to do this?  I do not want to use varchar since I want a fixed length with dots at the end.  Any ideas?

Thanks,
Alan

View Replies !   View Related
BCP XML Format File Fails If Column Name Contains Spaces - Looks Like A Bug
 

Hi,

I am using the following command
master..xp_cmdshell 'bcp Staging.dbo.TableA format nul -f "\CommonTableA.xml" -x -r"" -c -t -T'
 
to generate a XML format file so that I could later use during import process and the structure of the table is
CREATE TABLE TableA
(
[First Name] AS VARCHAR(50),
LastName as VARCHAR(50)
)
 
it fails, it throws Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I change the column name by removing space it works.
Is there a work around for it, the reason being the Tables and format files has to be generated on the fly as its a requirement and I want to use the XML format instead of FMT type.
 
Thanks
Sathish

View Replies !   View Related
DTS- Destination Column Name Has Spaces Causing Error
I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server.  I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error.  I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others.  DBA 101 here).  How do I beat this?


I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error.  Wrapping them with [] doesn't work.  I think this is a
limit on Advantage.


When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error.  I am afraid of upgrading Ace32.dll and not
break other things.

 

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

View Replies !   View Related
Ragged Right Export Trimming Off Leading Spaces In Last Column
I have to send a field delimited file which is exported from our SQL server to another agency.  The format is very strict.  It has to be 57 chars for the total record with CR/LF at the end of every column.  There can be no exceptions. 

In the past, I did a normal fixed width export from SQL Server 2000 and chose the end of line markers as CR/LF.

We have newly upgraded to SQL Server 2005 and everything is workign great except my simple DTS file export.  With the DTS changes, a normal fixed width export will not put the CR/LF at the end of the rows which means that they reject our file.  That means I have to do a ragged right export.  When I do this, it trims the trailing spaces out of my last column before adding the CR/LF to the end or each row.  If it is supposed to be fixed width, why is this trimming my trailing spaces off of there?  You would think that it would leave my last column as a width of 5 regardless of spaces.   Those trailing spaces must be there for the file to work properly.

This makes absolutely no sense to me.  Perhaps someone can shed some light on this or explain me what I am doing wrong.

View Replies !   View Related
How To Deal With Nvarchar Column
Hi,
 
My company is starting to use nvarchar columns in our database products. We just found out that, suppose table T1 has a my_nvarchar_col column, and there is a row containing a unicode text say "some Chinese", if you want to select that row, you have to append "N" in front of the unicode constant in the "WHERE" clause. That is:
 

select * from T1
where my_nvarchar_col = N'some Chinese'
 
will return that row, while
 

select * from T1
where my_nvarchar_col = 'some Chinese'

 
will return NOTHING.
 
This brings us a huge problem - we have tens of thousands of such queries in our existing PowerBuilder code base. Do we have to go through all of them to add "N" to the "WHERE" clause? Is there a way we can set some attribute of SQL Server so that we do not need to do that?
 
Project stalled and I am under extreme pressure so please help ASAP!!

View Replies !   View Related
Removing Duplicate Value From One Column.
Hi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.

(I know distinct)
Thanks in advance.

Minesh.

View Replies !   View Related
Removing Characters From Column
I have a table called exchange and field called address. The rows(1400+) in the field look like:
MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;

How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...

View Replies !   View Related
Removing Identity Column
How to remove identity property from a column throught SQL statement?

View Replies !   View Related
Removing Characters From A Column
Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.

Thanks!
Lisa

View Replies !   View Related
Removing Text From Column Name
Hi all
i want to remove text from my column name using query.
for example
i have the product name like "silver 8' trampoline pack "
i need to remove "silver 8' " and want to display only trampoline pack
similarly if I have product name like "gold 8' trampoline pack"
i need to display only trampoline pack.
can anybody help me in this regard?

thanks

View Replies !   View Related
Removing Identity Column
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?

The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?

View Replies !   View Related
Removing Numbers From A Column
 

Hi,
 
I have the following tables :
 



Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
 
Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);
 
The final data looks like this :
 
Cary zzz      NULL
01 Jack       NULL
02 Tommy      NULL
03 Ricardo    NULL
04 Jack       NULL
Les zzz       NULL
05 Tim        NULL
 
 



 
1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column
 



Code Block
Expected Output :

Jack       NULL
Tommy      NULL
Ricardo    NULL
Jack       NULL
Tim        NULL
 
 



 
Can anyone help me please with the query?
 
thanks.
 

View Replies !   View Related
Clustered Index On Nvarchar Column Or Int...
Users can approach their userprofile on my site using: www.mysite.com/name=peterName is a unique value within my database (db type: nvarchar(50))Now, I have created a clustered index on the username column.However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance....Is that true or is there a better way to solve this issue?

View Replies !   View Related
How To Add Newline In Column Of Type Nvarchar
Hi, I want to add a newline in a content of sql column (using t-sql and not asp.net textbox) so when content is being rendered in a .net textbox I get separate rows, so insteadcol1 col2 col3 I woule like to havecol1col2col3Thanks

View Replies !   View Related
Need To Count And Rank NVARCHAR Column
Assuming that I have a table similar to this simplified version:

CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));

How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:

field1 count
some value 24
another word 20
a value 5

(and so on...)

How can I do this?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

View Replies !   View Related
Modify A Column From Nvarchar(50) To A DateTime
Hello,

I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.

This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)

However, I have to change this column to a DateTime without destroying the data.

Is there any easy way to write some script or use studio management to change this.

Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.

The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.

I get this following error message:
- Unable to modify table.  
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any suggestions would be most grateful,

Thanks,

Steve

View Replies !   View Related
Removing Partial String From Column
how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the front

View Replies !   View Related
Removing Characters From Column Data
How can I remove characters that appear to have the effect of carriage returns or tabs, and they appear at the end of the data in a column. They look like small boxes lined up along side each other. I cannot find a way to clear these out of a particular column and when I move data from the table to a file it creates blank rows where ever these boxes appear.

Any help would be appreciated.
Thanks

View Replies !   View Related
Removing Non-numeric Digits From Column
Using the following:

select hl, substring(hl,1,patindex('%/%',hl)) as test
from appointment

returns

hl test
A PCM/RODRIGUEZ A PCM/
Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/
W DENTAL/DUNDON W DENTAL/
Y LAB
Y PCM/NEMES F/U Y PCM/
W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/
W FIRM-A/SILVER/ABBOUD IIW FIRM-A/

I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....

View Replies !   View Related
Removing Identity Property Of Column
How to remove an Identity property for an Identity Column

View Replies !   View Related
Removing All Hypertext Links From A Column
Is this possible to do using regular expressions in sql 2005?

View Replies !   View Related
Comparing Nvarchar(max) Column Using Like To Non-ASCII Range
Our database defines the long_value column as nvarchar(max).  I want to find out which rows actually contain non-ASCII characters in that column, but this clause also returns rows with only ASCII characters:where long_value like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

 What am I doing wrong?

View Replies !   View Related
How Can I Save Big File To NVARCHAR(Max) Column In The Database?
helo..
 I want to save file it size about 200MB to the database and after that

I want to get it from the database, the colmun type that i want to

save the file to it is NVARCHAR(Max).

I am using MS sql server 2005.

I was using this code to do this with Image Or Ntext column type but

it dose not working with NVARCHAR(Max) column?

 

 

Function SaveFileToDB(ByVal FileName As String, ByVal TblName As String, ByVal FldName As String, ByVal ColumnIDName As String, ByVal RowID As String) As Boolean

Try

Dim addEmp As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT @Pointer = TEXTPTR(" & FldName & ") FROM " & TblName & " WHERE " & ColumnIDName & " = '" & RowID & "'", MyConnection)

Dim trParm As SqlClient.SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)

trParm.Direction = ParameterDirection.Output

addEmp.ExecuteNonQuery()

'''''''''''''''''''''''''''''''''''''

Dim bufferLen As Integer = 1048576

Dim appendToPhoto As SqlClient.SqlCommand = New SqlClient.SqlCommand("UPDATETEXT " & TblName & "." & FldName & " @Pointer @Offset 0 @Bytes", MyConnection)

Dim ptrParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)

ptrParm.Value = trParm.Value

Dim photoParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)

Dim offsetParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)

offsetParm.Value = 0

Dim fs As IO.FileStream = New IO.FileStream(FileName, IO.FileMode.Open, IO.FileAccess.Read)

Dim br As IO.BinaryReader = New IO.BinaryReader(fs)

Dim buffer() As Byte = br.ReadBytes(bufferLen)

Dim offset_ctr As Long = 0

Do While buffer.Length > 0

photoParm.Value = buffer

appendToPhoto.ExecuteNonQuery()

offset_ctr += bufferLen

offsetParm.Value = offset_ctr

buffer = br.ReadBytes(bufferLen)

My.Application.DoEvents()

Loop

br.Close()

fs.Close()

Return True

Catch ex As Exception

MyErrStr = ex.Message

Return False

End Try

End Function

 

 

Public Function ReadFileFromDB(ByVal MyCommandText As String, ByVal FileColumnNumber As Integer, ByVal DSTFileName As String) As Boolean

Try

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(MyCommandText, MyConnection)

Dim stream As IO.FileStream

Dim writer As IO.BinaryWriter

Dim bufferSize As Integer = 1048576

Dim outByte(bufferSize - 1) As Byte

Dim retval As Long

Dim startIndex As Long = 0

Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()

reader.Read()

stream = New IO.FileStream(DSTFileName, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)

writer = New IO.BinaryWriter(stream)

startIndex = 0

retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)

Do While retval = bufferSize

writer.Write(outByte)

writer.Flush()

startIndex += bufferSize

retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)

My.Application.DoEvents()

Loop

writer.Write(outByte, 0, retval - 1)

writer.Flush()

writer.Close()

stream.Close()

reader.Close()

Return True

Catch ex As Exception

MyErrStr = ex.Message

Return False

End Try

End Function


 

View Replies !   View Related
Convert Column From NVARCHAR(MAX) To Money And Then Back Again.
 

I need to always have a formatting of 999,999,999.00 in a column called  PropertyMap9.
 
PropertyMap9 is always a nvarchar(max)
 
How do I SET a value using a CONVERT or CAST to accomplish this ?
 
Why does this give me a syntax error ?   The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error.  I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still  maintaining the nvarchar(max) datatype in the column.  HELP!
 

update PropertyMapValues



set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)

set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))

END

 

Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59

Incorrect syntax near '='.

View Replies !   View Related
How Do I Insert A String Value With Quotes Into A Nvarchar Column
I am reading data from another data source and storing it in the sqlce database.  Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company).  When I try to insert the values with the single quotes, it throws an exception.  The code I use to insert the records is as follows:

 

cmd.CommandText = "INSERT sy_company " +

" (company_id, company, co_name, companyid) " +

"VALUES(" +

"'" + dtSYCompany.Rows[x]["company_id"] + "'," +

"N'" + dtSYCompany.Rows[x]["company"] + "'," +

"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +

"'" + dtSYCompany.Rows[x]["companyid"] + "')";

cmd.ExecuteNonQuery();

 

When the company name (co_name) has a single quote in it, I get the error.  How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?

 

Thanks so much!

View Replies !   View Related
Adding CRLF To Nvarchar(max) Column Text
A SqlServer 2005 db has a table with an nvarchar(max) column containing text with paragraph format.  When displayed in a Windows form textbox each paragraph exhibits CRLF.  When the table is opened in Management Studio the paragraph text is separated with two boxes at each CRLF. 

I would like to insert an additional CRLF (or whatever is required) so that when viewed in a textbox each paragraph has a blank line separating it from the next paragraph. Much easier to read!!!

Can this be done?  If so, how?   Thanks in advance for any help you can provide.

View Replies !   View Related
Removing Commas From Column - Now It's A Mile Long
Hello,

I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:

CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?

I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.


Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails

ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------

Example query and result using REPLACE:

select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails

NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------

(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns

Thanks

JM

View Replies !   View Related
Com Exception When Removing ErrorOutPut Column From The Collection.
 

OutCollection[1] holds the erroroutput columncollection.

metaData.OutputCollection[1].OutputColumnCollection.RemoveObjectByID(errOutputCol.ID);

some times 1 Or 2  columns gets deleted, after that the exception is raised.

View Replies !   View Related
Nvarchar Column Text As Email Unicode Content
Hi,
i have a table with a nvarchar column,i want to send this column value as unicode content to customer mail box , but when i send it a mail with '?' customer receive , how can i accomplish this?
thanks

View Replies !   View Related
Pass NVARCHAR (or Other String-type) Var As A Column Or Table Name In SQL
Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.

Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?

Here's a basic idea of what I'd like to do:

DECLARE @myVar AS NVARCHAR(50)

SELECT *
FROM @myVar

or

DECLARE @myVar AS NVARCHAR(50)

SELECT @myVar
FROM MyTable



I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...

View Replies !   View Related
Storing A Carriage Return Or VbCrLf In An NVARCHAR Column
Is it possible to store a carriage return value or VbCrLf in an NVARCHAR column ?

I need to display a textbox in Reporting Services using one row, but showing multiple lines as a result of a few carriage returns.

View Replies !   View Related
Full-Text On HTML Stored In Nvarchar(MAX) Column
What is the best way of using the Full-Text feature on HTML?
I want to only search the text and omit the html tags.

If that involves storing as a different format, can someone tell me the best way of doing that?
I'm very new to sql and especially full-text.

Thanks.

View Replies !   View Related
Hiding Or Removing Column Output From Select Statement
I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved