Make one db field two in result set 1

[tgtfranz]

I have a field (CustomerName) that also contains a store number. So it would look something like this:

CustomerName#StoreNumber

I need to display these items as separate fields. I have been using a combination of string functions in SQL but am not having a lot of luck. I want to display everything to the RIGHT of the #. This should not be this difficult.

Please help.

Frustrated.

 

[AlexCuse]

Hi Frustrated.

Try this out, it should show you waht you need to do:

declare @test varchar(50)
set @test = 'CustomerName#StoreNumber'

select left(@test, charindex('#', @test) -1 )
select right(@test, charindex('#', reverse(@test)) - 1 )

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

Incidentally if you are in a postion where you can change the structure of your database, I would never consider storing data this way - you are asking for problems later. Customer name should be in at least 4 fields (first, middle, last and suffix (for jr, etc.)) and store name should bein a sepaarate field called StoreName. Trust me, if you think it's hard to get the storename out of this mess, wait until you try to get customers by last Name.

Questions about posting. See faq183-874