Saturday, March 29, 2008

Formatting Data By Using CHARINDEX And SUBSTRING

Let's say you have names stored in the format [XYZ, ABC] but would like it to be [ABC XYZ]
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work

CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))

INSERT INTO Names
SELECT 'XYZ, ABC',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL


UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)


SELECT * FROM Names

No comments: