Replace Text
-
hi , first i would thank you for help i have a column in table contains a code for students the code format is 11-1-07-0001 what i want , i want to replace the first tow digits from 11 to 01 without any efection to the other characters
MD_NADA
-
hi , first i would thank you for help i have a column in table contains a code for students the code format is 11-1-07-0001 what i want , i want to replace the first tow digits from 11 to 01 without any efection to the other characters
MD_NADA
-
hi , first i would thank you for help i have a column in table contains a code for students the code format is 11-1-07-0001 what i want , i want to replace the first tow digits from 11 to 01 without any efection to the other characters
MD_NADA
This is not something that happens to me every day... for once i can finally give help to someone else and not be the one asking the question :)
SELECT YourStudentNumberColumn AS [Test], '01' + SUBSTRING(YourStudentNumberColumn,3,LEN(YourStudentNumber) - 2) AS [TestAfterUpdate]Notice the3in thesubstring, this is the start position. TheLEN(YourStudentNumber) - 2is the amount of character to use. So it will start at position 3 being the first '-' in your student number and end at the end of the student number as it gets the length of the YourStudentNumber subtracts 2 as you start at position 3 (the first '-'). If this is unclear run the query in sql and change the'- 2'to '- 3' and you will see what it does. theSUBSTRINGworks like this. Substring(YourValue, StartPosition(1 for beginning of your value), AmountOfCharacters) I added the select statement so that you can make sure that this is the correct value that you need. If it is... you can run it like this for theUPDATE:UPDATE YourTableName SET YourStudentNumberColumnName = '01' + SUBSTRING(YourStudentNumberColumnName,3,LEN(YourStudentNumber) - 2)"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
-
This is not something that happens to me every day... for once i can finally give help to someone else and not be the one asking the question :)
SELECT YourStudentNumberColumn AS [Test], '01' + SUBSTRING(YourStudentNumberColumn,3,LEN(YourStudentNumber) - 2) AS [TestAfterUpdate]Notice the3in thesubstring, this is the start position. TheLEN(YourStudentNumber) - 2is the amount of character to use. So it will start at position 3 being the first '-' in your student number and end at the end of the student number as it gets the length of the YourStudentNumber subtracts 2 as you start at position 3 (the first '-'). If this is unclear run the query in sql and change the'- 2'to '- 3' and you will see what it does. theSUBSTRINGworks like this. Substring(YourValue, StartPosition(1 for beginning of your value), AmountOfCharacters) I added the select statement so that you can make sure that this is the correct value that you need. If it is... you can run it like this for theUPDATE:UPDATE YourTableName SET YourStudentNumberColumnName = '01' + SUBSTRING(YourStudentNumberColumnName,3,LEN(YourStudentNumber) - 2)"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
thank you very much , this is what i want i dont have any words to say but i am thankfull for any one in the world liks to help the others
MD_NADA