Function returning string with spaces in between each character, issues when trying to remove spaces

Question

Okay so i found what was wrong, it was in the sql convert function. I was converting to varchar(30) when it was supposed to be nvarchar(30). To see the difference. What is the difference between varchar and nvarchar?

I guess this might be a silly question but i can't seem to be able to remove some sort of space from a string returned by this function. I guess the first real question is why is this function returning a string like this "1 2 3 4 5 6 7 8 9" when we're saving to the database like this "1234789". Second question is why something like this

Replace("1 2 3 4 5 6 7 8 9","\p{Zs}","")

or

Replace("1 2 3 4 5 6 7 8 9"," ","")

Is not working, i have a feeling its because of the characters inbetween look like spaces but are not.

My Watch

    Private Shared Function GetEncryptedPmtField(lhID As Integer, accountId As Integer, fieldName As String)
        Dim openKeyCmd = String.Format("OPEN SYMMETRIC KEY somekey DECRYPTION BY PASSWORD = '{0}'",     ConfigurationManager.AppSettings("Somethingsomething"))
        Const closeKeyCmd As String = "CLOSE SYMMETRIC KEY somekey"

        Dim command As New SqlCommand()
        command.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("Something").ConnectionString)
        command.CommandType = CommandType.Text
        command.CommandText = String.Format("{0} SELECT convert(varchar(30),decryptbykey({1})  FROM  paymentacct WHERE lh_id = @lhid AND id = @id ; {2}", openKeyCmd, fieldName, closeKeyCmd)
        command.Parameters.AddRange({New SqlParameter With {.ParameterName = "lhid", .Value = lhID}, New SqlParameter With {.ParameterName = "id", .Value = accountId}})
        command.Connection.Open()
        Dim baNum = command.ExecuteScalar().ToString()
        command.Connection.Close()
        Return baNum
    End Function

If they're not spaces, then what are they? Set a breakpoint on the link and view the contents of the variable in hex. — Chris Dunaway, Sep 06 '12 at 16:44
Call `Char.GetNumericValue()` on each char of the string; I bet your database contains NVarChar, UCS-16, or something like that, not Unicode. — Dour High Arch, Sep 06 '12 at 17:09
As @DourHighArch said, you have some encoding problems. Don't try to solve it with regex or replace. Solve your encoding problem. — L.B, Sep 06 '12 at 17:13
I would be interested in seeing the object type returned by command.ExecuteScalar(). MSDN says it returns a generic object, it will be possible to find out what type is actually being returned and then better understand its ToString() method. — J Collins, Sep 06 '12 at 17:15

Brian Rizo · Accepted Answer · 2012-09-06T17:43:40.817

0

Thank you everyone for the help, here is the fix. So, the issue was in the type I was converting the encrypted field to. I was using varchar instead of nvarchar.

  SELECT convert(varchar(30),decryptbykey(keyname) FROM  paymentacct

to

 SELECT convert(nvarchar(30),decryptbykey(keyname))  FROM  paymentacct

Note the varchar -> nvarchar change in the convert function

edited Sep 06 '12 at 17:43

answered Sep 06 '12 at 17:35

Brian Rizo

838
9
14

paparazzo · Answer 2 · 2012-09-06T17:08:46.020

-1

Give Regex \s a try

System.Diagnostics.Debug.WriteLine(Regex.Replace("12 3  4   5   ",@"\s+",""));

If you are using String.Replace then it is not going to recognize "\p{Zs}" as a space.
You need to be using Regex. The question is not tagged Regex.

Are you saying in the database it is the correct value?

edited Sep 06 '12 at 17:08

answered Sep 06 '12 at 17:00

paparazzo

44,497
23
105
176

While that does work for the example you put up it does not work in this instance. http://i.imgur.com/qCroz.png – Brian Rizo Sep 06 '12 at 17:07
Then you need to post actual data. Do a copy paste of the problem data. Most likely it is a weird space. – paparazzo Sep 06 '12 at 17:10
Straight from the watch | Name | Value | Type| Regex.Replace(GetEncryptedPmtField(28,142,"routing"),"\p{Zs}","") | "1 2 3 4 5 6 7 8 9" | String – Brian Rizo Sep 06 '12 at 17:11
Those spaces in you comment are regular char(32). What is in watch may be formatted. I mean like copy paste closer to the actual output. – paparazzo Sep 06 '12 at 17:20
@BrianRizo Also, set your watch to display in hex. That should help. – Chris Dunaway Sep 07 '12 at 14:25

Function returning string with spaces in between each character, issues when trying to remove spaces

Okay so i found what was wrong, it was in the sql convert function. I was converting to varchar(30) when it was supposed to be nvarchar(30). To see the difference. What is the difference between varchar and nvarchar?

2 Answers2