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
 
Ad

Advertisements

Joined
Mar 3, 2021
Messages
243
Reaction score
29
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

Top