October 9, 2008 at 6:46 am
I have an expression in a Crystal Report that takes spaces, apostophes and dashes and converts them into underscores. If the length of the field is less than 6 characters,
it also adds the text "PAD" to the end.
I'm trying to figure out how to convert that to SQL syntax for an update/select I'm trying to do. Can anyone help?
Here's the expression in Crystal:
select UpperCase (Replace (Replace (Replace ({Personnel.LAST_NAME},' ','_'),"'" ,'_'),'-' ,'_')) + IIf (Length ({Personnel.LAST_NAME})<6,'PAD','')
Thank you.
R.A.
October 9, 2008 at 7:20 am
Have a look at the following code. You want the entire statement that creates the cleanedColumn column.
-Luke
CREATE TABLE #test (
id INT IDENTITY,
testval VARCHAR(15),
description VARCHAR(50)
)
INSERT INTO [#test]
SELECT 'test','no spaces no bad chars less than 6'
UNION
SELECT 'te,st','less than 6 with comma'
UNION
SELECT 'te st','less than 6 with spaces'
UNION
SELECT 'te''st','less than 6 with an appos'
UNION
SELECT 'testtest','no spaces no bad chars more than 6'
UNION
SELECT 'te,sttest','more than 6 with comma'
UNION
SELECT 'te sttest','more than 6 with spaces'
UNION
SELECT 'te''sttest','more than 6 with an appos'
SELECT id, [testval], [description],
CASE WHEN LEN(REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_')) < 6 THEN REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_') + 'PAD'
ELSE REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_')
END AS cleanedColumn
FROM [#test]
DROP TABLE [#test]
October 9, 2008 at 8:52 am
Thanks! That worked very well! Now I'm just trying to add an underscore if there's a hyphen and I keep getting a message about the Len argument -> The len function requires 1 argument(s).
SELECT id, [testval], [description],
CASE WHEN LEN(REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_')) < 6 THEN REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_')) + 'PAD'
ELSE REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_')
END AS cleanedColumn
FROM [#test]
I tried just adding '-','_' where it was necessary.
Thank you!
R.A.
October 9, 2008 at 9:12 am
You need to make sure you're calling the replace function each time as it take 3 params...
param one, the string to check
param 2 the string to replace
param 3 the string to be replaced
For each character to be replaced, you need to recall the function. You added the ,'-','_' but you didn't add another replace(.
SELECT id, [testval], [description],
CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_') < 6 THEN REPLACE(REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_') + 'PAD'
ELSE REPLACE(REPLACE(REPLACE(REPLACE([testval],' ','_'),'''','_'),',','_'),'-','_')
END AS cleanedColumn
FROM [#test]
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply