ksytrian ksytrian - 6 months ago 16
Vb.net Question

Removing first character from a column if it starts with 0

I have an oracle table,

a_abc
that have three columns (
name
,
ID
and
address
). 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

ConnectOracle()
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) & "') ")


Console.WriteLine(header)
Console.WriteLine(header.Trim({"0"c}))


Try
mySQLcmd.CommandText = SQL.ToString
mySQLcmd.ExecuteNonQuery()

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

The ltrim function should do the trick:

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

EDIT:
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) & "') ")