How to convert a database data in predefined format and generate output in text format

Joined
Feb 10, 2022
Messages
1
Reaction score
0
I am having a SQL database having the attributes First_Name; Last_Name; Sex; Address etc. For reporting to the authority I have an fixed format like
For First name: FN(no of characters in first name)First_Name
For example the first name Daniel should be like FN05Daniel
For example the last name Bose should be like LN04Bose
Is there any way using SSIS the same can be accomplished and the output can be generated in text format??
If not SSIS please suggest any probable mechanism to get the result. I am just learning coding so please don't mind if its a silly question.
Thanks
 
Joined
Mar 3, 2021
Messages
240
Reaction score
30
I'm not sure about SSIS, but you can easily do this within a SQL statement. In the following, the database is Test and the table is pran, which has two columns: first_name and last_name, both nvarchars.

SQL:
SELECT CONCAT('FN', FORMAT(LEN([first_name]), 'D2'), [first_name]),
      CONCAT('LN', FORMAT(LEN([last_name]), 'D2'), [last_name])
  FROM [Test].[dbo].[pran]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,780
Messages
2,569,611
Members
45,286
Latest member
ChristieSo

Latest Threads

Top