SQL parsing a carriage return delimited column

I've encountered this in the past and coworker helped me figure out a way to do it.  I'm positing this for my own memory but maybe it will help someone else too!

I was trying to pull a specific line from a database, but the column contained Carriage Delimited data.  As you can see "passwords" is not your normal database entry.  

In my situation, the first 4 digits are system generated, and the last 4 digits are manually generated.  So, if a password change was required, the only 4 digits i know are the first 4.

So I wanted to pull the exact line out of the system to obtain the password.

The problem is, doing a "select from passwords" would return the entire column into a single variable, treating every password as just one long huge single password.

> select * from pinsets where pinsets_id = 2

+------------+--------------------------+-------------+----------+----------+ | pinsets_id | passwords | description | addtocdr | deptname | +------------+--------------------------+-------------+----------+----------+ | 2 | 10001500 | testpins | 0 | | 10012400 10023400 10034400 10045400 +------------+--------------------------+-------------+----------+----------+ 1 row in set (0.00 sec)

I needed a way to search for the first 4 digits of the the password, and then have the system parse out the last 4 digits and give me just the line.

I want to search for "1000" and have the system return "10001500"

This was what I used

select MID((select passwords from pinsets where description = 'testpins'), INSTR ((select passwords from pinsets), "1000"),8);

The MID command pulls everything out of passwords into a single file, then I do an INSTR (instring) to find the first instance of 1000, and then pull all 8 characters (the first 4 characters '1000' followed by the last 4 characters '1500')

Running this command gave me the result I needed.

+---------------------------------------------------------------------------------------------------------------------------------+ | MID((select passwords from pinsets where description = 'testpins'), INSTR ((select passwords from pinsets), "1000"),8) | +---------------------------------------------------------------------------------------------------------------------------------+ | 10001500 | +---------------------------------------------------------------------------------------------------------------------------------+

This is not perfect, but it worked for my situation.  I had a degree of difficulty finding information on line for this situation, so I wanted to post what I found in case it helped someone else.