I’m working with a database table that has a column storing values in this pattern: “0000-00-0000”. For instance, one of my records contains “9421-05-2018”.
I want to transform this data from “9421-05-2018” into “009421-0005-2018”. Basically, I need to add “00” at the beginning of the first part (“9421”) and the second part (“05”) of the string.
Can someone help me figure out how to do this in SQL Server 2005? I have around 300 records that need this same transformation applied to them.
Any suggestions would be really helpful!
Another approach involves using the REPLACE and RIGHT functions combined with string manipulation. You can split the original string at each dash, pad each segment individually using RIGHT(‘000000’ + segment, desired_length), and then concatenate them back together. For your specific case, you can use the following SQL statement: UPDATE your_table SET your_column = RIGHT(‘000000’ + LEFT(your_column, CHARINDEX(‘-’, your_column)-1), 6) + ‘-’ + RIGHT(‘0000’ + SUBSTRING(your_column, CHARINDEX(‘-’, your_column)+1, 2), 4) + ‘-’ + RIGHT(your_column, 4). This method handles variable-length segments more reliably than fixed SUBSTRING positions, which is important if your data has inconsistent formatting across the records.
interesting challenge! have you considered what happens if some of your existing records already have different lengths in those segments? like are all first parts exactly 4 digits or do some vary? also curious - is this a one-time data cleanup or something you’ll need to handle regularly for new records coming in?
u can use SUBSTRING and CONCAT functions for this. try something like CONCAT(‘00’, SUBSTRING(your_column, 1, 4), ‘-00’, SUBSTRING(your_column, 6, 2), ‘-’, SUBSTRING(your_column, 9, 4)) in your update statement. just make sure to backup ur data first incase somethng goes wrong with the transformation.