How to insert "-" in existing records

[victor64]

Hello, I am using VB.NET 2005 and MS ACCESS 2003

My database contains data in the following format:

1305000248767

I need to insert "-" in several location to obtain the following format

1305-00-024-8767

How do I append the records to insert the "-" at these locations.

Thanks,

Victor

 

[JuggaloBrotha]

Pull the data out as a string, then re-assign the string using .SubString() to insert the hyphen(s) in the correct places. For Example:

Dim myString As String = "123456"
myString = myString.SubString(0I, 3I) & "-" & myString.SubString(3I, 3I)
'myString is now "123-456"

 

[victor64]

How to insert "-" in field data

Hello,

Thanks for your reply, I'm afraid I can't assign mystring to a field value.
Do you have another approach?

This is what I have so far, but can't figure out the mySQL_Statement, or perhaps I need to use a For Next stament to go over each record??

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

'dataset object
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN")
objConnection.Close()

Thanks

 

[MattP]

I'd use the substring function for T-SQL. Here's an example of formatting a social security number.

SELECT SUBSTRING(emp_ssn, 0, 3) + '-' + SUBSTRING(emp_ssn, 4, 2) + '-' + SUBSTRING(emp_ssn, 6, 4) AS SSN
FROM employee_table

I believe (don't use Access) that instead of SUBSTRING you use MID (same parameters) and '&' to concatenate rather than '+'.

 

[victor64]

Matt,

Thanks for your input, I believe SSN will be a new field created automatically. I was able to make the conversion, but the code I used was much longer, I will also try it with your code.

I initially made a mistake the data in the field didn't have the first 4 characters, basically it was in two separate fields:

Field 1: 1340 and Field 2: 001188989, I modified the code to obtain 00-118-8989

Now I need to do a check with a second table which contains

1340-00-118-8989(1)
1305-00-198-8989
1340-00-118-8989(1)
1315-00-108-7777
etc...

How do I start my search after the fifth position to check against data in the table which contains the 00-118-8989 format and only take into consideration the 11 chatracters ( will also need to avoid the (1) data from the other table).

I propbably should open a new thread, but since you're familiar with what I'm trying to do, I thought it would be easier to get some feedback.

Thanks,

Victor

 

[MattP]

You want to use these 2 values to do a JOIN on the tables?

SELECT *
FROM TableA a JOIN TableB b ON MID(a.column, 5, 11) = b.column

 

[victor64]

Searching for data to display from two tables a link field.

How would you use this statement with a where clause?
I will search the tables bases on values entered in a textbox.

Thanks,

Victor