ksytrian ksytrian - 2 years ago 113
Vb.net Question

Removing first character from a column if it starts with 0

I have an oracle table,

that have three columns (
). The data for this table will be extracted from a txt file. How to remove the first character in ID column if it starts with number '0'. I need a VB solution. This is what Ive done so far now

Private Sub ReadData()
Dim rowvalue As String
Dim cellvalue(20) As String
Dim header As String = "ID"
Dim streamReader As IO.StreamReader = New IO.StreamReader("D:\local_costctr.txt")
'Reading CSV file content
While streamReader.Peek() <> -1
rowvalue = streamReader.ReadLine()
cellvalue = rowvalue.Split("|") 'check what is ur separator

mySQLcmd.Connection = myConn

If SQL.Length > 0 Then
SQL.Replace(SQL.ToString, "")
End If

SQL.Append("insert into a_abc (name ,ID ,address) ")
SQL.Append(" values ('" & cellvalue(0) & "' ,'" & cellvalue(1) & "','" & cellvalue(2) & "') ")


mySQLcmd.CommandText = SQL.ToString

Catch ex As Exception
MessageBox.Show("Error " & ex.Message.ToString)
End Try

End While
End Sub

If there is more than one leading zero, I want all of them removed.

Answer Source

The ltrim function should do the trick:

SELECT name, LTRIM(id, '0'), address
FROM   a_abc

Now that the question was edited, I see you refer inserting the data, not querying it. The same solution could be applied on an insert too though:

SQL.Append("insert into a_abc (name ,ID ,address)  ")
SQL.Append(" values ('" & cellvalue(0) & "', LTRIM(" & cellvalue(1) & ", '0'), '" & cellvalue(2) & "') ")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download